的Excel 2013 VBA多个电子邮件地址到Outlook
问题描述:
我在Excel 2013编码我已经得到了客户数据的表会增加,在数量上随着时间的推移减少取决于我的积极老将案例:的Excel 2013 VBA多个电子邮件地址到Outlook
A列 - 姓氏
B列 - 名字
C列 - 电子邮件地址
列d - 等等......
我需要的代码来引用C列,并在其中放置所有邮件在BC C的一个Outlook电子邮件。我创建的代码(通过我的研究)只允许在Outlook的TO,CC或BCC字段中使用硬编码的电子邮件地址 - 在多个条目之间使用分号。我的问题是,电子邮件地址的数量会因电子表格中的记录数量而异,因此对它们进行硬编码是没有用的。下面的代码具有我需要的所有功能,但电子邮件问题除外。
Sub SendBasicEmail()
Dim olApp As Outlook.Application
Dim olEmail As Outlook.MailItem
Set olApp = New Outlook.Application
Set olEmail = olApp.CreateItem(olMailItem)
With olEmail
.BodyFormat = olFormatHTML
.Display
.HTMLBody = "<h3>Testing</h3><br>" & "<br>" & .HTMLBody
.Attachments.Add "xxx/test.pdf"
.To = ""
.BCC = ""
.Subject = "Test Message"
'.Send
End With
End Sub
答
我只是在列上循环,并使字符串持有由分号分隔的地址。
Sub SendBasicEmail()
Dim olApp As Outlook.Application
Dim olEmail As Outlook.MailItem
Set olApp = New Outlook.Application
Set olEmail = olApp.CreateItem(olMailItem)
'set your range as needed, i chose one named "recipients"
bc_r = ""
For each cl in range("recipients")
bc_r = bc_r & "; " & cl.Value
Next cl
With olEmail
.BodyFormat = olFormatHTML
.Display
.HTMLBody = "<h3>Testing</h3><br>" & "<br>" & .HTMLBody
.Attachments.Add "xxx/test.pdf"
.To = ""
.BCC = bc_r
.Subject = "Test Message"
'.Send
End With
End Sub
答
通过片材1的内容的代码将环路(只是改变到片材(“whateveryoucalledyoursheet”))和保持单元行。
Sub SendBasicEmail()
dim ws as worksheet, y
Dim olApp As Outlook.Application
Dim olEmail As Outlook.MailItem
set ws = sheets(1)
for each y in ws.range("A1:A" & ws.range("A1").SpecialCells(xlCellTypeLastCell).row)
Set olApp = New Outlook.Application
Set olEmail = olApp.CreateItem(olMailItem)
With olEmail
.BodyFormat = olFormatHTML
.Display
.HTMLBody = "<h3>Testing</h3><br>" & "<br>" & .HTMLBody
.Attachments.Add "xxx/test.pdf"
.To = ws.range("A" & y.row)
.BCC = ws.range("C" & y.row)
.Subject = "Test Message"
' use display to check the email out before you send
.display
'.Send
End With
next y
end sub
经过测试,完全符合我的需求。非常感谢您的帮助。希望我可以投你一票,但这是我第一次发布Stack Overflow。 – Agnew