Sending email is a routine task for all kind of industry. it may be reporting, marketing, data science, communication, retail industry and so on. It will become a tedious job when same kind of email needs to be sent over multiple stakeholders.
Obviously, sending same kind of email with different kind of attachment is a quite time consuming and boring activity. Specially, when it comes to email marketing to send emails to various recipient including attachments specific to their needs.
Obviously, sending same kind of email with different kind of attachment is a quite time consuming and boring activity. Specially, when it comes to email marketing to send emails to various recipient including attachments specific to their needs.
Thus, there will be a requirement to automate this task and send emails to respective stakeholder at one button click. we can automate this task in excel itself. Using few lines of code in excel macro will serve the purpose if sending emails.
Here is the code which will be use to send email from excel:
Below code is written to send email for a single email ID
Sub Email_With_Attachment()
'Declare Variables
Dim oApp As Object
Dim oMail As Object
'Disable screen updating
Application.ScreenUpdating = False
'Creates and shows the outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
'Assign recipients email IDs
.To = "Test@gmail.com"
'Assign CC email IDs
.Cc = "cc@gmail.com;cc1@gmail.com"
'Assign CC email IDs
.Bcc = "bcc@gmail.com;bcc1@gmail.com"
'Pass subject line which you required
.Subject = "Email VBA code"
'Pass email body matter which you want to set in email with your signature by adding new line
'using & vbNewLine & code as written in below code
.Body = "Hi All" & vbNewLine & vbNewLine & "This is test email sample code in excel VBA"
'Assign attachment file path
.Attachments.Add "C:\Users\Dreams\Desktop\Test\Test.pdf"
'Below code will be used to show created email. To send email replace Display with Send
.Display
End With
Set oMail = Nothing
Set oApp = Nothing
'Enable screen updating
Application.ScreenUpdating = True
End Sub
Now, We will write a VBA code which will send emails to multiple email IDs. Before writing a code we will need details of recipients, CC, BCC, Subject and attachment file path. Refer below snap which is having required details. you can create your own entries as per your need.
Make sure that above table is create in Sheet1 as we are using reference of Sheet1 in our code.
Given below is block of code which be used to send emails to multiple email IDs:
Sub VBA_Code_To_Send_Email_To_Multiple_EmailId()
' Author: Dreams24
' Written for VBA Tricks and tips blog
'Declare Variables
Dim oApp As Object
Dim oMail As Object
Dim i As Integer
'Disable screen updating
Application.ScreenUpdating = False
For i = 2 To ThisWorkbook.Sheets("Sheet1").Range("A1").End(xlDown).Row
'Creates and shows the outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
.To = ThisWorkbook.Sheets("Sheet1").Range("A" & i)
.Cc = ThisWorkbook.Sheets("Sheet1").Range("B" & i)
.Bcc = ThisWorkbook.Sheets("Sheet1").Range("C" & i)
.Subject = ThisWorkbook.Sheets("Sheet1").Range("D" & i)
'Set email body including your signature
'Make use of "& vbNewLine &" code to add new line in your email body
.Body = "Hi All" & vbNewLine & vbNewLine & "Test email to test email macro VBA code"
'Assign attachment file path
.Attachments.Add ThisWorkbook.Sheets("Sheet1").Range("E" & i)
'Below code will be used to show created email. To send email replace Display with Send
.Display
End With
Set oMail = Nothing
Set oApp = Nothing
Next i
'Enable screen updating
Application.ScreenUpdating = True
End Sub
You can share this post with others looking of similar kind of requirement. Also, you may contact me for your VBA automation projects.