Send Mail From Excel via Outlook
HTML-код
- Опубликовано: 23 фев 2023
- Excel macro/VBA code available in this post: excelmacroclass.blogspot.com/...
We can integrate Excel with other Microsoft Office applications using VBA. In this video we see how to send an email from Excel (and via Outlook) using VBA macros.
You can find many other macro examples and Excel VBA learning materials (including the Excel VBA Guide for Beginners) in the blog under the link: excelmacroclass.blogspot.com
And yet, if you want more, you can find various Excel applications of different nature in the other blogs of the Excel Macro Mania saga:
Excel Macro Fun (excelmacrofun.blogspot.com/)
Excel Macro Business (excelmacrobusiness.blogspot.com/)
Excel Macro Sports (excelmacrosports.blogspot.com/)
Great... It worked smoothly... Thanks a lot sir
So nice of you to take the time to help others!!!! Thank you
This is exactly what I was looking. Thanks. This helps a lot.
This was super easy to follow and it worked easily! Thank you so much
thank you, this video is wonderful
This is so cool!
You Sir just got a like & subscribe
Good Stuff :)
Thank you for the help. Am curious, is there a way to add a cell reference in the in the message so that the email is slightly customized. Example: Dear CLIENT, where the CLIENT is changed based on the cell reference. So if row 1, 2 and 3 had John, Alice, and Tim, creating and clicking a send email button would use their name, so for row 1, an email like: Dear John, blabla. I hope my question is understandeable.
That's covered in this other video: ruclips.net/video/XpGzWvSr9j4/видео.html
You can also find the macro in this blog article: excelmacroclass.blogspot.com/2023/05/send-multiple-emails-from-excel-vba.html
Thanks for video.
What will be code to choose sensitivity label or sublabel.
OutMail.Sensitivity = olConfidential
It accepts one of the following: olConfidential, olNormal, olPersonal, olPrivate
Great video. Is it possible to configure the VBA so that it sends email to pre selected email adresses + email adresses from ex. Range(B1:B9)?
Yes, that's covered in this other video: ruclips.net/video/XpGzWvSr9j4/видео.htmlsi=4tGntsqn42nCJU2j
Hi thank you for your code and explanation. I would like to copy a table in the email body, I'm using .Body = Range("A11:H12").Select but it's not working. Do you know how can I paste several cells?
First you need to convert your range with data to an HTML table - see this post: excelmacroclass.blogspot.com/2023/04/convert-range-to-html-table-excel-vba.html
Then you need to use the HTMLBody property instead of just Body. For example:
OutMail.HTMLBody = "NameCountryJamesUSA..."
What if I need to send to multiple emails + multiple CC emails?
Also can I have 2 cells for the subject and range for the body, at 2 columns and 5 rows?
Yes, add another column for CC email addresses, and as many rows as different combinations/recipients.
Great! Can you please share code if we need to add a excel file pr pdf file with this email
Dim attachment_FileNameAndLocalPath As String
attachment_FileNameAndLocalPath = "C:\Users\username\Documents\myattachment.pdf" 'replace with yours
OutMail.Attachments.Add attachment_FileNameAndLocalPath
Thanks for the video. Very Powerful and magical.
However, after repeating the same process, I had an error at Set OutMail = OutApp.CreateItem(0) - "Cannot Create the email because a data file to send and receive messages cannot be found. check your settings in this microsoft outlook profile. What do you suggest will fix this?
I think there's a problem with your Outlook profile, don't really what it is. You may also try to Set OutMail = Nothing always, just in case that creates a conflict. But I still think it's something with your Outlook profile.
Instead of giving range for b1, b2, b3. How can I put for whole Column as to, whole column as subject and whole Column as email body. What will be the coding for this
The whole column? So you have information across many cells in one column that you want to add to the email? Well, it that's the case, you should rather concatenated everything in one string (separated with vbLf or HTML breaks and then add the string with HTMLBody. You probably want to loop through each cell with data in that column and add the value to that string variable. You can also put the column contents easily into a 2D array, convert to 1D array, and use VBA Join function.
Hi, What's the code to mark the email as "official" with regards to the "Microsoft Azure Information Protection" Document classifying pop up. this works but I have to still click "official" then send once the pop up comes up - if I need to send hundreds of emails, I don't want to classify each induvial email.
I think that's setup by your company, I mean via Azure but using company's security classification (something like Public, Internal, Confidential, etc,... I see you have Official there), and that cannot be changed with VBA unfortunately. You should be able to set a default thought, I believe my company emails are all set by default to "Internal", so maybe you can do that - talk to you IT admin/helpdesk, etc. The only related things VBA allows to change is the sensitivity (personal, private, confidential, etc), importance (high, low, etc), and things like that.
is it possible to put this within an if statement? for example If item is expired then send email reminder?(would have a separate column to show if item is expired). My goal is to have a list of items that need updating and as they come to expire I can run the VBA daily to have it automatically check for expired items and if its expired then it would email who is responsible for that item
Yes, you can put one or more If statements anywhere in the code to control the flow. I recommend you check the video about conditions and loops in the Excel VBA tutorial series: ruclips.net/video/RFGTYQwjs90/видео.htmlsi=wZmAYzKvdrcX4bFL
Can we add in this process the company official branding guidelines , such as the company logo in the right corner header and bellow it a thick blue line … etc ?
It looks like you want to add your company's email signature or something like that so use the code below or modify as needed:
With OutMail
.Display
signature = .HTMLBody
.To = "recipientemail@outlook.com"
.Subject = "email subject"
body = "Dear Customer,... bla bla"
.HTMLBody = body & signature
'.Send 'uncomment to send
End With
@@ExcelMacroMania I am also trying to do this
I was able to add in my signature, but now the code line that reads
.Display
Signature = .HTMLbody
.SentOnBehalfofName
And it's not changing to my other email account, I think because the email is being displayed first (to grab the HTML signature). Is there any work arounds?
Thanks for your video and blogs! so helpful to learn this
@@lorrainewhiting1102 I think you should be using SendUsingAccount instead of SendOnBehalf:
OutApp.SendUsingAccount = OutApp.Session.Accounts("youremail@outlook.com")
Hi can you let me know how to add the email sensitivity as confidential in this code
OutMail.Sensitivity = olConfidential
It accepts one of the following: olConfidential, olNormal, olPersonal, olPrivate
@@ExcelMacroMania it's not working I tried could you please help. I have the following code in excel-
Sub Send_Multiple_Email()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")
Dim OA As Object
Dim msg As Object
Set OA = CreateObject("Outlook.Application")
Dim i As Integer
Dim last_row As Integer
last_row = Application.WorksheetFunction.CountA(sh.Range("A:A"))
For i = 2 To last_row
Set msg = OA.createitem(0)
msg.to = sh.Range("C" & i).Value
msg.cc = sh.Range("D" & i).Value
msg.Subject = sh.Range("E" & i).Value
msg.htmlbody = sh.Range("F" & i).Value
If sh.Range("G" & i).Value "" Then
msg.attachments.Add sh.Range("G" & i).Value
End If
msg.display
Next i
MsgBox "Mails sent"
End Sub
Great video! If you don't mind, how would you edit the code so an attachment can be sent? I tried adding a line (.Attachment = Range("B4").Value) and pasting the file location and name, but it is not working...
You were close, it's .Attachments.Add Range("B4").Value and it's explained in this other video: ruclips.net/video/XpGzWvSr9j4/видео.htmlsi=ADwE21KauMY0H-do
Thank you my man!@@ExcelMacroMania
@@ExcelMacroManiaI have another scenario I'm praying you can advise on. I can't figure out how to attach other files, such as a pdf? I couldn't get it to work using a .pdf as an attachment...
@@jon2646 The code I gave you before should work, but you need to give the full local path to the pdf file. For example:
OutMail.Attachments.Add "C:\Users\userid\documents\mypdffile.pdf"
If that doesn't work it may be because you have the file in OneDrive, check if that's the case... when you get the full name of the file it starts with ... , and you should get the local version starting with C:\
@@ExcelMacroMania you are the best! I will try that out and let you know if that works!
hey could u show a tutorial on how to attach a file with this macro?
That's covered in this other video: ruclips.net/video/XpGzWvSr9j4/видео.htmlsi=BrWbVvK7C7KXFuyP
And it's basically adding the following line of code with attachment file path for as many attachments as you want to add.
OutMail.Attachments.Add "C:\users\username]documents....\myattachment.pdf"
Thank you. I'm trying to figure out the code so that the body copies more information from different cells. For example, .Body = range("B3:H27").value but this code doesn't work. Any suggestions so that the body of the email has all the information in the range of the excel sheet from B3 thru H27? Thank you
You can use the Excel formula CONCAT to concatenate everything in just one cell and then get that one cell in the body. The only problem is that you get all together (no space). You can also do it with VBA and add a space or a line break between the values in each cell in that range. For example, you can use a loop as follows:
For Each cell In Range("B3:H27")
bodyStr = bodyStr & " " & cell.Value 'or vbNewLine instead of " " to add a line break each time
Next cell
OutMail.Body = bodyStr
I appreciate your help. I don't have much experience so I think I'll have to contact someone that can do it for me. Thanks @@ExcelMacroMania
Hi, where i can find the microsoft outlook library, in the reference section my excel doesnt show me that library, how i can do? where i can download it?
i dont want to use CDO library
That may be because you installed Excel stand-alone (without the other Microsoft Office apps, or without Microsoft Outlook), or because your version simply didn't include that (I think it happens on Macs). You can use the Office repair tool from your control panel (if you have a licensed installation), or try to get a new Office package.
@@ExcelMacroManiaI am a student not a professional, I just want to try this code. No free version available for Outlook??
@@Oum10001 I don't know if there is a free version for students... maybe online version is free. You can also try sending via Gmail, check out this other video: ruclips.net/video/T7G_EnKyS_0/видео.htmlsi=dV3fbWSuTu0ZhvsH
I have Outlook, but Microsoft Outlook 16.0 Object Library isn't appearing in my references. How can I fix that?
And you also don't have 15.0 or 14.0? In such case, you will need to either reinstall Office package with everything or try to find just that library online. If you bought a license, check with Microsoft's support.
@@ExcelMacroMania I didn't have a license. I have it now, thanks.
Sir I have excel sheet the sheet have 3 advisor name when u send the main using vba the name not mentioned on mail body how to add automatic name in mail body plz help
Put the advisor name into a variable, the signature if you need, and the main body, and concatenate them all. You can use vbLf to add a line break. See this example - it is covered in this other video: ruclips.net/video/XpGzWvSr9j4/видео.htmlsi=kLzWmcnIsIonhg5I
bodyHeader = "Dear " & Range("B2").Value & ","
bodyMain = Range("C2").Value
bodySignature = "Sincerely," & vbLf & "Excel Macro Mania"
OutMail.Body = bodyHeader & vbLf & vbLf & bodyMain & vbLf & vbLf & bodySignature
Hi ! how to add a picture (ex *.JPG) in a body of the message ?
You need to use HTMLBody and the HTML tag img along with src - see below an example:
imgpath= "C:\Users\Administrator\Documents\pic.jpg" 'change with your picture path
OutMail.HTMLBody = ""
Note that Chr(34) is to add double quoutes inside the double quotes in Excel. This one also works:
OutMail.HTMLBody = ""
If you want to add text too, you can have it between tags in the HTMLBody, or use just Body property too:
With OutMail
.Body = "text only body here or add it to HTMLBody between tags, etc"
.HTMLBody = .Body & ""
End With
Hi thank you for your video's! Is it possible to create an email that is not from the default email account in my outlook ? .From doesn't work ;-)
Sure it is possible. Indeed From does not work. You need to use SendUsingAccount, which is somehow the equivalent to "From" in a sense. The code line would look like the following:
OutMail.SendUsingAccount = "youremail@domain.com"
@@ExcelMacroMania thank you for your answer! I tried to put this function line in the VBA macro, but it doesn't work anywhere. Can you help me further?
@@checkerrivado9420 Check how many accounts you have and that indeed you have a second (or even more) account.
Dim OutApp As New Outlook.Application
OutAccounts = OutApp.Session.Accounts.Count
Display OutAccounts in a message box or Debug.Print to see how many. I guess you have 2. Then try this below along the rest of the code from the video to send from the 2nd account.
OutMail.SendUsingAccount = OutApp.Session.Accounts(2)
I believe the same works with the email address, but try putting it inside Accounts, as follows:
OutMail.SendUsingAccount = OutApp.Session.Accounts("emailaddress@yourdomain.com")
@@ExcelMacroMania I don't get it working. I have 4 accounts. I get errors on both lines that you write in the answer
@@checkerrivado9420 I don't know why it doesn't work, this always work for me in 2013, 2016, maybe 2019 too, but did not test on 365 because I cannot add other accounts on my work laptop. Maybe you have 365...google it and see if there are any limitations or write to customer support.
Hi there, I'm getting error (Run-time error '429'; ActiveX component can't create object) how to fix this..
Did you add the library? maybe you are using the wrong library or an outdated one...? Please check the video again I think I explained which library you need to add around minute 1:30-1:40, and that works well for Excel 2016.
I tried this code but i am getting error "Object doesn't support this property or method". What should i do??
Make sure you add the Microsoft Outlook library to the references. If already done, then check the OutMail object variable is properly declared/defined. Check the code in this post: excelmacroclass.blogspot.com/2021/12/send-email-from-excel.html
When i run the code, it shows a popup saying "Outlook is not installed on this computer ", although i have it installed. Please provide a solution
That's weird, never heard of such problem. Maybe you can try to re-install Outlook or the whole Office package. Or maybe is installed but you never run it before, so in that case you need to set it up with an email account.
Hello, great help. Can you show how to code loop? For sending multiple emails to diff recepient and different emails bodies.
Thank you so much
I will probably make a video about that soon, but it's already explained in this blog post: excelmacroclass.blogspot.com/2023/05/send-multiple-emails-from-excel-vba.html
The video is also there now: Send Multiple Emails From Excel (ruclips.net/video/XpGzWvSr9j4/видео.html)
do you know why? in reference I Can't see outlook
I don't know why you can't see that. Maybe you did not install all the components in Excel/Office (custom installation). I always had those libraries. Nevertheless, you can probably still run the macro and send email declaring and creating the mail object with CreateObject as indicated below:
Dim OutApp As Object
Set OutApp = CreateObject("Outlook.Application")
Hi , Actually My Query Is where we are adding the From Mail id In this Code
That's not needed if you only have 1 email account in Outlook (that's the case in this video), or you want to use the first or default account. It will send it from that email account.
But if you have more than 1 email account and you want to send from one of them (which might not be the default account), then you need to use the following: OutMail.SendUsingAccount = "youremail@domain.com"
Can you please clarify this function
@@abdullahalmaghrabi7734 SendUsingAccount is used to send the email from another account (which is not the default one). You only need this when you have more than one account added to outlook, otherwise, you do not need it, because it will always send the email from the only one email account that you have. Also, while there is a "To" property, and "CC", "BCC" , etc, there is NO a "From" property; SendUsingAccount is probably the closest equivalent to "From", in a sense. And again, it's only needed if you have more than one email account in Outlook.
It shows that the macro might not be available in the workbook
Check the macro security settings, ... maybe macros are disabled. Otherwise, if the macro is a public Sub procedure added to an standard module, it should be available from any workbook.
How to place an email signature from outlook to body of excel? thank you!
That's covered in this other video: Send Multiple Emails From Excel (ruclips.net/video/XpGzWvSr9j4/видео.html)
@@ExcelMacroMania how to send with signature? because if I replace .display to .send it will send but it send an empty body
I guess you mean to keep your default signature that is added automatically when creating a new email. That signature is part of the body, and is replaced with whatever you put in .Body = "bla bla...". So the trick is to first display and get the HTMLBody, which is just the default signature, and then concatenate that when adding the body, but as HTMLbody - see below:
With OutMail
.Display
signature = .HTMLBody
.To = "recipientemail@outlook.com"
.Subject = "email subject"
body = "Dear Customer,... bla bla"
.HTMLBody = body & signature
'.Send 'uncomment to send
End With
Hi, if we add body without the dot new lines are not displayed. How can we fix?
How do you send it to multiple email addresses? : )
That's covered in this other video: ruclips.net/video/XpGzWvSr9j4/видео.html
Also, you can read the article here: excelmacroclass.blogspot.com/2023/05/send-multiple-emails-from-excel-vba.html
thanks alot
@@ExcelMacroMania
I won’t to change the regards name we have lost off name how to change
I believe you want to keep your automatic signature while adding the body so here's the way to do it:
With OutMail
.Display
signature = .HTMLBody
.To = "recipientemail@outlook.com"
.Subject = "email subject"
body = "Dear Customer,... bla bla"
.HTMLBody = body & signature
'.Send 'uncomment to send
End With
How to add file in attachment by vba.
Dim attachment_FileNameAndLocalPath As String
attachment_FileNameAndLocalPath = "C:\Users\username\Documents\myattachment.docx" 'replace with yours
OutMail.Attachments.Add attachment_FileNameAndLocalPath
How to add multiple emails id's in to?
.To = "firstemail@gmail.com; secondemail@gmail.com; thirdemail@gmail.com...."
Or if you are asking about sending multiple separate emails to each account check this other video: ruclips.net/video/XpGzWvSr9j4/видео.html
kalo error activex gmna ya kak? udh coba ubah ke automatic activex nya ttep gabisa di run
I guess you get the error in the line with CreateObject. So give it a try with early binding instead. It's explained at the beginning of the video. Something like this:
Dim OutApp As New Outlook.Application
Dim OutMail As Outlook.MailItem
However, maybe it's because don't have permissions for the Outlook library, which could happen for example at work, due to policies. Check with your system admin.
How to add table from excel under body?
First you need to convert the range with data into an HTML table . This is explained in this blog post:
Then you have to use HTMLBody and the HTML table string representing your data. Something like this:
OutMail.HTMLBody = htmltable
where htmltable = "NameCountryJohnUSA..."
Can i get this code
Sure, the code is here: excelmacroclass.blogspot.com/2021/12/send-email-from-excel.html
Can you share this code
Please check this post: excelmacroclass.blogspot.com/2021/12/send-email-from-excel.html
hi sir, how to bold text ?
Use the property HTMLBody instead of Body and create a string with the necessary HTML bold tags - for example:
OutMail.HTMLBody = "Bold text here..."
how to schedule an outlook email through vba excel
This will schedule to send the mail at 13:00 today (email stays in the Outbox until delivery):
OutMail.DeferredDeliveryTime = "5/22/2023 13:00"
You can also use DateAdd function to send after a number of seconds, minutes, hours, days,..., this example sends after 10 minutes for now (use "s" for seconds, "n" for minutes, "h" for hours, "d" for days, "m" for months)
OutMail.DeferredDeliveryTime = DateAdd("n", 10, Now)
What if we Need to send through gmail?
There are several options - I will try to make a video to cover that:
1. Add Gmail account to Microsoft Outlook and use the same macro adding SendUsingAccount property to send from the gmail account (if not the only or default one in Outlook)
2. Using the CDO library for Windows to send directly through Gmail (no need of Outlook at all)
3. Create your own custom Google API with apps script from within the same Gmail account associated with your gmail address
@@ExcelMacroMania make video on second and third option..they sound interesting
@@rehanrafiq2985 Will definitely do, but cannot tell you when yet.
Salve si può inserire un allegato
Dim attachment_FileNameAndPath As String
attachment_FileNameAndPath = "C:\Users\username\Documents\myattachment.pdf" 'replace with yours
OutMail.Attachments.Add attachment_FileNameAndPath
@@ExcelMacroMania Grazie mille se tutto funziona te lo farò sapere
Sir, if email addresses are different in column B and cc in column C and subject in column D and multiple attachment in column E f g h i ... recipients name in column "A" what will be the vba code for that
That's how is done in this other video, check it out: ruclips.net/video/XpGzWvSr9j4/видео.htmlsi=0iYA5UOAR7fQfJsf
So if r is the row number then for your example:
With OutMail
.To = Range("B" & r).Value
.CC = Range("C" & r).Value
.Subject = Range("D" & r).Value
.Attachments.Add Range("E" & r).Value
.Attachments.Add Range("F" & r).Value
.Attachments.Add Range("G" & r).Value
...
.Display 'optional
'.Send 'uncomment to send
End With