Welcome to Excel Avon
Send Email with the Attachment
DOWNLOAD USED EXCEL FILE FROM HERE>>
In today’s post, we will show you how to send email with attachment in Excel VBA as well as how to use the Userform options that you have already read about. But in this post we will not work with Userform but with Module
This post is similar as we covered in the previous post How to Send Email with Outlook Automation in Excel VBA
While working with Excel files in our daily life, we often need to send email of the file as an attachment. In this article, I will show how you can develop a macro to send email with attachment from Excel.
As mentioned above, the Attachment object is not a string. It is actually a collection, meaning it holds other objects. Specifically, it contains attachment objects.
Here we have written email id, CC ,BCC message text subject, and path of attachment files in excel sheet, we will send this message text to Outlook with the help of VBE.
Send Email with the Attachment in Excel VBA
As we taught you how to insert a module when we were working with modules, as we have learned in many posts so far, you can insert modules. Let us teach you how we insert the module. Let’s understand, then we have to go like last time, first go to the Developer Tab, then click on the option of Visual Basic as shown in the image below.
On opening in VBE, you have to go to Insert and then Module has to be inserted, as can be seen in the image.
Go to Tools and then select References as shown in the below screenshot. scroll down in the Reference Object library and select “Microsoft Outlook 16.0 Object Library” to make it available for Excel VBA.
Once the module is inserted, and the library is activated, we’ll write a subroutine to Sub Send Email with Outlook ().
Sub SendWithAttachment() End Sub
We will Declare the variable Outlook Application
Sub SendWithAttachment() Dim olApp As New Outlook.Application End Sub
Now, to write the email, we declare one more variable as “MailItem”.
Sub SendWithAttachment() Dim olApp As New Outlook.Application Dim mItem As MailItem End Sub
Now we will declare some variable source as a string. Here CC, BCC subject, message, and file path are all declared under string source.
Sub SendWithAttachment() Dim olApp As New Outlook.Application Dim mItem As MailItem Dim Email As String Dim CC As String, BCC As String Dim Subject As String, msg As String Dim FilePath As String End Sub
To launch a new email, we need to set the reference to our previous variable as “CreateItem.”
Sub SendWithAttachment() Dim olApp As New Outlook.Application Dim mItem As MailItem Dim Email As String Dim CC As String, BCC As String Dim Subject As String, msg As String Dim FilePath As String Set mItem = olApp.CreateItem(olMailItem) End Sub
Here we will declare the range of email written in the sheet here
Sub SendWithAttachment()
Dim olApp As New Outlook.Application
Dim mItem As MailItem
Dim Email As String
Dim CC As String, BCC As String
Dim Subject As String, msg As String
Dim FilePath As String
Set mItem = olApp.CreateItem(olMailItem)
Email = Sheet1.Range("D4").Value
End Sub
As we have declared the range of email above, similarly we will declare the range of all properties like message text, subject file path range. the location of the file path from where the attachment will be taken.
Sub SendWithAttachment()
Dim olApp As New Outlook.Application
Dim mItem As MailItem
Dim Email As String
Dim CC As String, BCC As String
Dim Subject As String, msg As String
Dim FilePath As String
Set mItem = olApp.CreateItem(olMailItem)
Email = Sheet1.Range("D4").Value
CC = Sheet1.Range("D5").Value
BCC = Sheet1.Range("D6").Value
Subject = Sheet1.Range("D7").Value
msg = Sheet1.Range("D8").Value
FilePath = Sheet1.Range("D9").Value
End Sub
Now we’ll use with statement ‘With mailobj’ and then use some function of mail item, Set mailitem.To to email, Set mailitem.CC to CC and Set mailitem.BCC to BCC, set mailitem.Subject to Subject, Set mailitem.body to msg.
Sub SendWithAttachment()
Dim olApp As New Outlook.Application
Dim mItem As MailItem
Dim Email As String
Dim CC As String, BCC As String
Dim Subject As String, msg As String
Dim FilePath As String
Set mItem = olApp.CreateItem(olMailItem)
Email = Sheet1.Range("D4").Value
CC = Sheet1.Range("D5").Value
BCC = Sheet1.Range("D6").Value
Subject = Sheet1.Range("D7").Value
msg = Sheet1.Range("D8").Value
FilePath = Sheet1.Range("D9").Value
With mItem
.To = Email
.CC = CC
.BCC = BCC
.Subject = Subject
.Body = msg
End with
End Sub
Using the attachment with mail item function, And in the last we will give a command which will be of send.
Sub SendWithAttachment()
Dim olApp As New Outlook.Application
Dim mItem As MailItem
Dim Email As String
Dim CC As String, BCC As String
Dim Subject As String, msg As String
Dim FilePath As String
Set mItem = olApp.CreateItem(olMailItem)
Email = Sheet1.Range("D4").Value
CC = Sheet1.Range("D5").Value
BCC = Sheet1.Range("D6").Value
Subject = Sheet1.Range("D7").Value
msg = Sheet1.Range("D8").Value
FilePath = Sheet1.Range("D9").Value
With mItem
.To = Email
.CC = CC
.BCC = BCC
.Subject = Subject
.Body = msg
.Attachments.Add FilePath, , , "Image File (Please check)"
.Display
End with
End Sub
Click on Run button
After running we will go to Outlook application and then there, we will see your sent email, we will go to outbox to see there we can see our sent trial email. Here you will see the subject ‘This is trial attachment email’
Go back to VBE, here send command will be replaced with display command.
Sub SendWithAttachment()
Dim olApp As New Outlook.Application
Dim mItem As MailItem
Dim Email As String
Dim CC As String, BCC As String
Dim Subject As String, msg As String
Dim FilePath As String
Set mItem = olApp.CreateItem(olMailItem)
Email = Sheet1.Range("D4").Value
CC = Sheet1.Range("D5").Value
BCC = Sheet1.Range("D6").Value
Subject = Sheet1.Range("D7").Value
msg = Sheet1.Range("D8").Value
FilePath = Sheet1.Range("D9").Value
With mItem
.To = Email
.CC = CC
.BCC = BCC
.Subject = Subject
.Body = msg
.Attachments.Add FilePath, , , "Image File (Please check)"
.Send
End with
End Sub
Display command shows the message before send email.
Therefore, I hope that you have understood How to Send Email with the Attachment in Excel VBA, maybe if you do not understand some options, then you can comment us, which we will answer soon and for more information, you can follow us on Twitter, Instagram, LinkedIn and you can also follow on YouTube.
DOWNLOAD USED EXCEL FILE FROM HERE>>
You can also see well-explained video here about How to Send Email with the Attachment in Excel VBA
