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.

Sub SaveAsDraft()
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 & ""    ' This would pull the first name and last name from the spreadsheet and generate the "To" e-mail address
myRecipients.Add (SendTo)

'   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
MsgBox myRecipient.Name
End If

End If

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=''></a></body></html>"

With objMailMessage
.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.
End With

Next count

End Sub


Office 365