blocks|key|4270578|text|这将忽略您区域中的空白单元格，假设您要忽略的值在您的区域A2+-+A196中。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4270579|在这里，ignore实际上意味着跳到"Else“，在那里你的循环将重新开始。如果IF语句告诉它在为空时什么也不做，那么它就被忽略了。下一行是"Next+Cell“，它将为您提供所需的结果。|4270580|Sub+SendMail()

Dim+objOutlook+As+Object
Dim+objMail+As+Object
Dim+ws+As+Worksheet

Set+objOutlook+=+CreateObject("Outlook.Application")
Set+ws+=+ActiveSheet

For+Each+cell+In+ws.Range("A2:A196")
If+cell.value+<>+""+Then++'If+NOT+blank,+do+this+(your+code)
Set+objMail+=+objOutlook.CreateItem(0)

++++With+objMail
++++++++.To+=+cell.Value
++++++.CC+=+cell.Offset(0,+1).Value
++++++++.Subject+=+cell.Offset(0,+2).Value
++++++++.Body+=+cell.Offset(0,+3).Value
++++++++.Attachments.Add+cell.Offset(0,+4).Value
++++++++.Attachments.Add+cell.Offset(0,+5).Value
++++++++.Attachments.Add+cell.Offset(0,+6).Value
++++++++.Attachments.Add+cell.Offset(0,+7).Value
++++++++.Attachments.Add+cell.Offset(0,+8).Value
++++++++.Display
++++End+With

++++Set+objMail+=+Nothing
Else+'If+IS+blank,+do+this+(next+cell)
End+If+
Next+cell

Set+ws+=+Nothing
Set+objOutlook+=+Nothing

End+Sub|code-block|syntax|javascript|4270581|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

This will ignore your blanks cells in your range, assuming the values you want to ignore are in your Range A2 - A196. 

Here, ignore really means to skip down to "Else" where your loop will start over. It is being ignored in the sense that the IF statement is telling it to do nothing when blank. The next line is "Next Cell" which will give you the desired result. 

<pre><code>Sub SendMail()

Dim objOutlook As Object
Dim objMail As Object
Dim ws As Worksheet

Set objOutlook = CreateObject("Outlook.Application")
Set ws = ActiveSheet

For Each cell In ws.Range("A2:A196")
If cell.value &lt;&gt; "" Then 'If NOT blank, do this (your code)
Set objMail = objOutlook.CreateItem(0)

 With objMail
 .To = cell.Value
 .CC = cell.Offset(0, 1).Value
 .Subject = cell.Offset(0, 2).Value
 .Body = cell.Offset(0, 3).Value
 .Attachments.Add cell.Offset(0, 4).Value
 .Attachments.Add cell.Offset(0, 5).Value
 .Attachments.Add cell.Offset(0, 6).Value
 .Attachments.Add cell.Offset(0, 7).Value
 .Attachments.Add cell.Offset(0, 8).Value
 .Display
 End With

 Set objMail = Nothing
Else 'If IS blank, do this (next cell)
End If 
Next cell

Set ws = Nothing
Set objOutlook = Nothing

End Sub
</code></pre>

blocks|key|4270614|text|尝试这种方式，并随时修改代码以满足您的需要，当然。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4270615|使用以下命令在工作表(“Sheet1”)中创建列表：|4270616|In+column+A+:+Names+of+the+people
In+column+B+:+E-mail+addresses
In+column+C:Z+:+Filenames+like+this+C:\Data\Book2.xls+(don't+have+to+be+Excel+files)|code-block|syntax|javascript|4270617|宏将遍历"Sheet1“中的每一行，如果B列中有电子邮件地址，C:Z列中有文件名，它将使用此信息创建一封邮件并发送该邮件。|4270618|Sub+Send_Files()
'Working+in+Excel+2000-2016
'For+Tips+see:+http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
++++Dim+OutApp+As+Object
++++Dim+OutMail+As+Object
++++Dim+sh+As+Worksheet
++++Dim+cell+As+Range
++++Dim+FileCell+As+Range
++++Dim+rng+As+Range

++++With+Application
++++++++.EnableEvents+=+False
++++++++.ScreenUpdating+=+False
++++End+With

++++Set+sh+=+Sheets("Sheet1")

++++Set+OutApp+=+CreateObject("Outlook.Application")

++++For+Each+cell+In+sh.Columns("B").Cells.SpecialCells(xlCellTypeConstants)

++++++++'Enter+the+path/file+names+in+the+C:Z+column+in+each+row
++++++++Set+rng+=+sh.Cells(cell.Row,+1).Range("C1:Z1")

++++++++If+cell.Value+Like+"?*@?*.?*"+And+_
+++++++++++Application.WorksheetFunction.CountA(rng)+>+0+Then
++++++++++++Set+OutMail+=+OutApp.CreateItem(0)

++++++++++++With+OutMail
++++++++++++++++.to+=+cell.Value
++++++++++++++++.Subject+=+"Testfile"
++++++++++++++++.Body+=+"Hi+"+&+cell.Offset(0,+-1).Value

++++++++++++++++For+Each+FileCell+In+rng.SpecialCells(xlCellTypeConstants)
++++++++++++++++++++If+Trim(FileCell)+<>+""+Then
++++++++++++++++++++++++If+Dir(FileCell.Value)+<>+""+Then
++++++++++++++++++++++++++++.Attachments.Add+FileCell.Value
++++++++++++++++++++++++End+If
++++++++++++++++++++End+If
++++++++++++++++Next+FileCell

++++++++++++++++.Send++'Or+use+.Display
++++++++++++End+With

++++++++++++Set+OutMail+=+Nothing
++++++++End+If
++++Next+cell

++++Set+OutApp+=+Nothing
++++With+Application
++++++++.EnableEvents+=+True
++++++++.ScreenUpdating+=+True
++++End+With
End+Sub|4270619|https://www.rondebruin.nl/win/s1/outlook/amail6.htm|offset|length|4270620|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|0|0|0|0|1F|0|0^^$0|@$1|2|3|4|5|6|7|X|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Y|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Z|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|10|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|11|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|12|8|@]|9|@$O|13|P|14|1|15]]|A|$]]|$1|Q|3|-4|5|6|7|16|8|@]|9|@]|A|$]]]|R|$S|$5|T|U|V|A|$W|N]]]]

Try it this way, and feel free to modify the code to suit your needs, of course..

Make a list in Sheets("Sheet1") with :

<pre><code>In column A : Names of the people
In column B : E-mail addresses
In column C:Z : Filenames like this C:\Data\Book2.xls (don't have to be Excel files)
</code></pre>

The Macro will loop through each row in "Sheet1" and if there is a E-mail address in column B
and file name(s) in column C:Z it will create a mail with this information and send it.

<pre><code>Sub Send_Files()
'Working in Excel 2000-2016
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
 Dim OutApp As Object
 Dim OutMail As Object
 Dim sh As Worksheet
 Dim cell As Range
 Dim FileCell As Range
 Dim rng As Range

 With Application
 .EnableEvents = False
 .ScreenUpdating = False
 End With

 Set sh = Sheets("Sheet1")

 Set OutApp = CreateObject("Outlook.Application")

 For Each cell In sh.Columns("B").Cells.SpecialCells(xlCellTypeConstants)

 'Enter the path/file names in the C:Z column in each row
 Set rng = sh.Cells(cell.Row, 1).Range("C1:Z1")

 If cell.Value Like "?*@?*.?*" And _
 Application.WorksheetFunction.CountA(rng) &gt; 0 Then
 Set OutMail = OutApp.CreateItem(0)

 With OutMail
 .to = cell.Value
 .Subject = "Testfile"
 .Body = "Hi " &amp; cell.Offset(0, -1).Value

 For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
 If Trim(FileCell) &lt;&gt; "" Then
 If Dir(FileCell.Value) &lt;&gt; "" Then
 .Attachments.Add FileCell.Value
 End If
 End If
 Next FileCell

 .Send 'Or use .Display
 End With

 Set OutMail = Nothing
 End If
 Next cell

 Set OutApp = Nothing
 With Application
 .EnableEvents = True
 .ScreenUpdating = True
 End With
End Sub
</code></pre>

<a href="https://www.rondebruin.nl/win/s1/outlook/amail6.htm" rel="nofollow noreferrer">https://www.rondebruin.nl/win/s1/outlook/amail6.htm</a>

I have an Excel sheet with email addresses, To, CC, Subject etc.

I have file paths to each attachment. These are statements. Some .PDF and some .XLSX depending on the request. Although I have the file path to each, some have multiple Columns E-L but not all rows will have a file path and not always a statement at the end of the path.

I need the VBA code to ignore blanks and missing files only attaching as found. This can be as many as 9 files or as little as one or none by row to recipient.

I cannot get it to run without error, in my Test environment, ignoring blank cells with no path or paths that do not have a file.

<pre><code>Sub SendMail()

 Dim objOutlook As Object
 Dim objMail As Object
 Dim ws As Worksheet

 Set objOutlook = CreateObject("Outlook.Application")
 Set ws = ActiveSheet

 For Each cell In ws.Range("A2:A196")

 Set objMail = objOutlook.CreateItem(0)

 With objMail
 .To = cell.Value
 .CC = cell.Offset(0, 1).Value
 .Subject = cell.Offset(0, 2).Value
 .Body = cell.Offset(0, 3).Value
 .Attachments.Add cell.Offset(0, 4).Value
 .Attachments.Add cell.Offset(0, 5).Value
 .Attachments.Add cell.Offset(0, 6).Value
 .Attachments.Add cell.Offset(0, 7).Value
 .Attachments.Add cell.Offset(0, 8).Value
 .Display
 End With

 Set objMail = Nothing
 Next cell

 Set ws = Nothing
 Set objOutlook = Nothing

End Sub
</code></pre>

This is my first VBA project.

Skip processing where values are blanks using Excel VBA

我有一个电子邮件地址，收件人，抄送，主题等Excel工作表。我有每个附件的文件路径。这些都是声明。一些.PDF和一些.XLSX取决于请求。虽然我有每个行的文件路径，但有些行有多个E-L列，但不是所有行都有文件路径，并且并不总是在路径的末尾有一条语句。我需要的VBA代码忽略空白和丢失的文件只附加发现。这可以是最多9个文件...

问使用Excel VBA跳过值为空的处理
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用Excel VBA跳过值为空的处理EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用Excel VBA跳过值为空的处理
EN