We can use Microsoft Excel to send and extract/read emails via Outlook or even via some other email service such as Google mail. In this post we see how to do that. But wait, why would we want to do that in the first place? We can use the data in Excel to address the email to a particular group, and with specific information stored in Excel, or send multiple emails at once, schedule emails, track email status, etc.
Send email via Outlook
We can integrate Excel with other Microsoft Office applications using VBA. Thus, we can target the Outlook application object from within Excel’s VBA project to send an email, among other things. For that we need first to declare object variables to store Outlook application and Outlook item (mail) objects. Then we use the function CreateObject to initialize Outlook and assign the Outlook Application object to the variable (e.g. OutApp). Now we can use the CreateItem method of the Outlook Application object to assign the first item to the variable OutMail. The index 0 is used for mail item type as per OlItemType enumeration. See more about enumerations here.
Alternatively, we could use early binding to declare/define the objects with less code. Early binding also helps to see the properties and methods of the objects while coding. But to do that, we need first to add the Microsoft Outlook Object library to the references of the VBA project.
Dim OutMail As MailItem
Using a With statement, we set properties of the mail item (OutMail) for recipient email address (To), email subject, and email body. We can add as many recipients as needed separating the email addresses with a semi-colon (;). You can also add attachments with Attachments.Add followed by the drive path and file name of the attachment file. The Display method shows the email in Outlook before sending. If we want to fully automate it, we have to use the Send method (and no need for Display). See below the macro to send email from Excel.
Sub SendMailFromExcel()
Dim OutApp As Object, OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = "recipient@outlook.com"
.Subject = “test”
.Body = "This is a test email sent from Excel"
.Display 'optional
'.Send 'uncomment to send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Send multiple emails
We can add as
many recipients as needed to the “To” property of the mail object, separated by
a semi-colon. However, if we want to send separate emails to each recipient, we
need to loop through the list of recipient emails and send an email to each of
them individually. Let’s see how to send multiple emails to recipients listed
in a worksheet in Excel. We can choose to send the same or a different subject,
body, or attachments to each recipient.
We need first to declare two object variables to represent the Outlook application and mail item, and then create the object (or use early binding), exactly as we did above. But now we need to loop through the list of recipients to get the email addresses and other info needed to send the emails. We start the loop from row 2 (to skip the headers), up to the last row with content in column B (where we have the list of email addresses for each recipient).
For each email recipient in the list we perform the following tasks. First we create a mail item object assigned to OutMail (index 0 corresponds to a mail item). Then use mail item properties to fill in necessary information such as recipient email, subject, body, and attachment, referring to the corresponding range/cell. We can choose to have the same or different subject, body, or attachment for each email recipient. In the full macro code below, we use the same subject, main body, and attachment (as per row 2), just changing the body header for each email recipient. In case we have a specific subject, body, or attachment for each email recipient, we just need to update the target range as follows:
OutMail.Attachments.Add Range("F" & r).Value
The recipient can be one or more email addresses separated by a semi-colon (;). We can also add CC and BCC recipients with the respective properties, use HTMLBody to add the email body in that specific format, and add more attachments simply repeating that line of code and pointing at the appropriate cell with attachment information. Note that the attachment requires a full path and name of the file to be attached.
Dim OutApp As Object, OutMail As Object, lastRow As Integer, r As Integer
Dim bodyHeader As String, bodyMain As String, bodySignature As String
Set OutApp = CreateObject("Outlook.Application")
lastRow = Cells(Rows.Count, "B").End(xlUp).Row
bodySignature = "Sincerely," & vbLf & "Excel Macro Mania"
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = Range("C" & r).Value
.Subject = Range("D2").Value
'email body with various components
bodyHeader = "Dear " & Range("B" & r).Value & ","
bodyMain = Range("E2").Value
.Body = bodyHeader & vbLf & vbLf & bodyMain & vbLf & vbLf & bodySignature
.Attachments.Add Range("F2").Value
.Display 'optional
'.Send 'uncomment to send
End With
Next r
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Send email with CDO (example Gmail)
We can also send an email from Excel with Gmail by adding the Gmail account to Outlook and following the exact same approach described earlier, just specifying to send using that account.
However, if we don’t want to use Outlook at all, we can also send an email from Excel via a Gmail account using the Collaboration Data Objects (CDO) API for Windows. First of all we need to add the Microsoft CDO for Windows 2000 library. We declare CDOmail and CDOconfig as objects and fields as a variant. Then we create the CDO message and configuration objects that are assigned to the respective variables and load the CDO configuration. We need to target certain CDO configuration fields to setup the connection with the SMTP server. These are the steps to setup the connection:
- Enable SSL Authentication
- Specify SMTP server (smtp.gmail.com)
- Specify SMTP port, which can be 465 or 25
- Enable SMTP authentication
- Indicate to use default settings to send the message
- Add sender Google email address
- Add Google account app password
IMPORTANT: We need to setup the Google account with
2-factor authentication to use this method. Then you can get an app password
(not the same as your Google account password) going to Google account ->
Security -> 2-step authentication to generate an app password for mail
application.
Finally, we assign that CDO configuration to the CDO message object and specify the sender and recipient email, subject, body, to send the email from Excel via the Gmail account. See the macro below.
Sub SendGmailFromExcel()
Dim CDOmail As Object, CDOconfig As Object, fields As Variant
Set CDOmail = CreateObject("CDO.Message")
Set CDOconfig = CreateObject("CDO.Configuration")
CDOconfig.Load -1
Set fields = CDOconfig.fields
With fields
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl")
= True
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver")
= "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport")
= 465
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate")
= 1
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing")
= 2 .Item("http://schemas.microsoft.com/cdo/configuration/sendusername")="email@gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword")
= "xxxxxxxxxx"
.Update
End With
With CDOmail
Set .Configuration = CDOconfig
.From = " yourgmail@gmail.com"
.To = "recipientemail@domain.com"
.Subject = "test"
.TextBody = "This is a test email sent from Excel
via Gmail"
.Send
End With
End Sub
Extract email from Outlook
We can also extract emails from Outlook in Excel. For that, we need to target the Outlook application object as we did before. But now, we also need to target the folder where emails are located (e.g., Inbox). Then we can choose to get all the emails in that folder, or have some conditions to filter for example only unread emails, or emails from a certain period, etc. The macro example below extracts all emails from the Inbox received since 01/01/2024.
Dim OutNamespace As Namespace, OutFolder As MAPIFolder, OutMail As Object
Dim r As Integer, cutDate As Date
Set OutFolder = OutNamespace.GetDefaultFolder(olFolderInbox)
cutDate = "01/01/2024"
For Each OutMail In OutFolder.Items
If OutMail.ReceivedTime >= cutDate Then
r = r + 1
Range("A" & r + 1).Value = OutMail.ReceivedTime
Range("B" & r + 1).Value = OutMail.SenderName
Range("C" & r + 1).Value = OutMail.Subject
Range("D" & r + 1).Value = OutMail.Body
End If
Next OutMail
On Error GoTo 0
Set OutNamespace = Nothing
Set OutApp = Nothing
The macro extracts the received date and time, sender,
subject, and body, for each email in the Inbox received after the given date. We can get other relevant information with the
corresponding properties, or download attachments. Similarly, we can target
other items such as calendar appointments, contacts, tasks, for example. We can
basically get all the information that we see in Outlook into Excel using the
same approach. So that’s how we manage emails in Excel using VBA macros.
No comments:
Post a Comment