November 8, 2013 - Warren Held
Generate Emails From Excel Using VBA
I was tasked with double checking that the hardware we had listed for the employees in our inventory management system was up-to-date. In order to do this I was going to send out a lot of e-mails. I like to live by the motto of “work smarter not harder”. I wrote a VBA script that would generate e-mails in Outlook and would pull information from the spreadsheet I had of the users equipment and use that info in the e-mails. Now I just have to figure out how to imbed images in an e-mail using VBA. If you have any questions or know how to imbed images in the body of the e-mail (not linking them with html to a web server) feel free to leave a comment.
Dim objOutlook As Object
Dim objMailMessage As Outlook.MailItem
Dim emlBody, SendTo As String
Dim wkbook As String
Dim range As Integer
Dim count As Integer
range = Application.WorksheetFunction.CountA(Sheets("Sheet1").range("A:A"))
Application.ScreenUpdating = False
For count = 2 To range
Set objOutlook = CreateObject("Outlook.Application")
Set objMailMessage = objOutlook.CreateItem(0)
Set myRecipients = objMailMessage.Recipients
SendTo = Cells(count, 1).Value & "." & Cells(count, 2).Value & "@email_address.com" ' This would pull the first name and last name from the spreadsheet and generate the "To" e-mail address
' Since I am using Outlook connected to a MS Exchange server that syncs with Active Directory, this will try to resolve the e-mail address.
' This way when I look at the generated e-mails in my draft folder I can quickly see which e-mail addresses are invalid.
' If the address is invalid it is supposed to open a message box alerting you, but that functionality doesn't seem to want to work.
If Not myRecipients.ResolveAll Then
For Each myRecipient In myRecipients
If Not myRecipient.Resolved Then
emlBody = "<html><body>Hello,<br /><br />We are going through our computer inventory and verifying if the equipment we have in our" & _
" system is the equipment you have in your possession. Our records show your computer model is a " & Cells(count, 5).Value & _
" with asset tag " & Cells(count, 6).Value & ". Please reply to this e-mail and let me know if the model and asset tag number" & _
" we have on file matches your equipment. If the asset tag number has faded to the point of illegibility please reply with" & _
" the manufacturer service tag and the number on the old asset tag (if present)." & _
"<br /><br />Thanks,<br /><br />Warren Held<br />IT Systems Administrator I<br />Phone: 000.000.0000 x000" & _
"<br /><a href='http://www.thecompany.com'>www.thecompany.com</a></body></html>"
.BodyFormat = olFormatHTML ' Set the body format to HTML. Now you can use HTML tags in the e-mail body for formatting.
.HTMLBody = emlBody
.Subject = "IT Computer Equipment Audit"
' .Display ' This command would display the e-mail on the screen. I have it commented out because I want them to be generated in the background.
.Save ' This saves the e-mails in the Drafts folder in Outlook so you can review them before sending them out.
' .Send ' Un-commenting this line would have Outlook send out the e-mails right away.