Send Mail From Excel via Outlook
HTML-код
- Опубликовано: 7 фев 2025
- Excel macro/VBA code available in this post: excelmacroclas...
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: excelmacroclas...
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....)
Excel Macro Business (excelmacrobusi...)
Excel Macro Sports (excelmacrospor...)
![Felix "Unfair" | [Stray Kids : SKZ-PLAYER]](http://i.ytimg.com/vi/Oswujxm2Ag0/mqdefault.jpg)
I thought it would take me 10h to learn how to automate a manual task that takes 15 minutes. You saved me!
So nice of you to take the time to help others!!!! Thank you
This was super easy to follow and it worked easily! Thank you so much
This is exactly what I was looking. Thanks. This helps a lot.
Many Thanks Excel Macro mania, Its worked :-)
thank you, this video is wonderful
There is a missing in tool reference Microsoft Outlook 16.0 in VBA. How to add an alternative or name change of it. How to do? I want to do it through coding and send messages from Outlook.
Great... It worked smoothly... Thanks a lot sir
This is so cool!
Hello ...kindly reply
I have followed the steps email is getting displayed on screen for second and getting disappeared and not receving email as well...where did i do the mistake
Hi Sir, Thanks for the explanation.
it's really very helpfull for me. I have 2 questions
1. how to send table - How can I select range for table
2. how can I send 1st sheet of excel via mail
Thanks for your support
1. Check this other video to see how to convert range to html and put it in a variable (e.g. rangehtml). Then you have to add html with OutMail.HTMLBody = rangehtml. Here's the video: ruclips.net/video/Ifc2I6wmWIk/видео.htmlsi=LU_Yci_g5bt6E6rA
2. You can use UsedRange to get the range in that sheet and then convert it to html as per point 1. If you have more things in the sheet such as charts, shapes, etc, that you want to send via email, is more complicated and you would need to loop through objects and maybe save as picture and send as attachment or also in the html with ... there are many options there.
@@ExcelMacroMania Hi Sir, Thanks for your support. I just started learning VBI macro. I have one request from my team. I need to do automation using Excel macro.
could you please support on this ? I can pay money for the support.
Thank you.
@@raghumahadeva3013 Unfortunately I do not have time to do support or do any other jobs. You can find VBA experts in freelancer, upwork, etc. You can also learn quickly following my tutorials here in the channel (start with tutorial for beginners playlist), and in my blogs: excelmacroclass.blogspot.com/
@@ExcelMacroMania okay sir. Thanks for your valuable inputs.
Love u bro
Kya isse gmail sw bhi bhej skte hai
What if the body compose of multiple cells. What should we do?
Simple and easy to follow video; helped me a great deal after failing miserably with PowerAutomate. Question - Can I select a specific Row and attach this in the email that goes out?
Yes, that's actually covered in this other video, check it out: ruclips.net/video/XpGzWvSr9j4/видео.htmlsi=XBwBw9wIQF82hOaB
Hello, thanks for the great video and instructions.
I have 2 questions:
1. How do you align the raw to the right or left?
2. How do you separate between sentences in the body, so each one of them will start in a new raw.
Thanks for your support.
1. To align content in the email you need to use HTMLBody property instead of just Body. Then you need to have the html alone or with css to align the content. For example:
OutMail.HTMLBody = "" & htmlbody1 & "" & ....
2. If you use HTMLBody as above in point 1, you can just add the break as htlml (""). If you use Body you can use vbLf as follows:
OutMail.Body = bodyHeader & vbLf & vbLf & bodyMain & vbLf & vbLf & bodySignature
Thanks for your help and kindness.
I wrote the Macro but from some reason the emails haven’t been sent.
What have I done wrong?
Can I send you a screenshot?
@@איוויקסשירותיהנדסהפיקוחוייעוץ But were they sent before? You just changed HTMLBody? where do you get the error? Check your code is correct comparing with macros here: excelmacrobusiness.blogspot.com/2024/02/excel-email-management.html
I don’t get error. I push the “send” button, it gives the feeling that emails were sent but there is no sign foe emails sent or received.
I get an error message saying that it cannot open wrong exchange account
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.
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.
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.
Or maybe check the default email in the settings. Sometimes is not Outlook.
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")
Excellent..thank you..i need to include a excel range as a part of the body of the email..can you help
1. Convert the range to an HTML table - see this video: ruclips.net/video/Ifc2I6wmWIk/видео.htmlsi=vuHo3HICieUehjb8
2. OutMail.HTMLBody = htmltable
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"
Thanks 🙏🏻 I have multiple mails in my outlook account, how I select the account I want to send the mail from ? .from?
OutMail.SendUsingAccount = OutApp.Session.Accounts("youremail@outlook.com")
@@ExcelMacroMania doesn't work, maybe because is the shared mail of my company
@@alexf2863 Yes, that could be the reason.
@@ExcelMacroMania but the res works!! Thanks you 😀
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.
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
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
Hello
I wrote the code for sending multiple emails in accordance with your great video.
I wrote:
Dim OutApp as new outlook. Application
By clicking on send button I got a message:
Compile error
User defined type not defined.
Please advise
User define type not defined usually means that you wrote some variable/object that Excel cannot understand because it has not been defined before. You should look at the yellow line of the compile error. It could be you didn't add the Outlook library to the references, but also that you mistyped some variable/object, so double-check the code here: excelmacroclass.blogspot.com/2023/05/send-multiple-emails-from-excel-vba.html
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.
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
Is it possible to create the same except having a range for the body information? or would there be a copy and paste function?
Yes, it's possible. You can add the range with data as an HTML table or as a picture.
1. As HTML table first convert range to HTML -> see this video: ruclips.net/video/Ifc2I6wmWIk/видео.htmlsi=z0rJg8mBckm4J1Sf
Then add OutMail.HTMLBody = htmltable
2. As a picture first convert the range to a picture see this video: ruclips.net/video/N7I-IPMe0no/видео.htmlsi=cSzY0k5P_wVdg-PA
Then add as picture attachment or in the email body with OutMail.HTMLBody = ""
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
Can we do it through outlook web instead of outllook app ? if we can please show us.
Not possible in outlook web, it does not support macros. But you can use a method explained in this other video: ruclips.net/video/T7G_EnKyS_0/видео.htmlsi=xnJ9mPwSbfHY_7qq
Can we add from address, because wanted to send from shared mailbox
OutMail.SendUsingAccount = OutApp.Session.Accounts("youremail@outlook.com") 'or the displayName or the index (1,2,..)
great idea,
what about if i want to send an excel attachment too?
attachmentFilePath = "C:\Users\\Document\attachment.pdf" 'REPLACE with your path and file!!!!!
OutMail.Attachments.Add attachmentFilePath
If i used it for zimbra mail, can it work?
This is only for Outlook. For other email provider use the code in this other video (example for Google mail, you will need to update properties for zimbra mail - smtp server, port, etc). Check that video here: ruclips.net/video/T7G_EnKyS_0/видео.htmlsi=i1LVt7rVUurRSUGf
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
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..."
You Sir just got a like & subscribe
Does it work for outlook365?
Yes, it does.
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.
How can I make part of the text bold?
Use HTMLBody along html code for example as follows:
OutMail.HTMLBody = "Text in bold"
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
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.
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.
Sir, I want to receive sales activities in Excel to my email address. So pls how to connect?
You can convert the range with sales data into an HTML table and add it to the email body with OutMail.HTMLBody = htmltable, where htmltable is your data as HTML, check this other video to convert range data to HTML table: ruclips.net/video/Ifc2I6wmWIk/видео.htmlsi=BYoyYge_1BJlxYrs
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.
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 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?
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
how can i do this to assign a task?
Dim OutApp As Outlook.Application, NewTask As Outlook.TaskItem
Set OutApp = New Outlook.Application
Set NewTask = OutApp CreateItem(olTaskItem)
NewTask.Display
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'm getting this same issue despite adding the correct library. I think it is related to my company's antivirus software blocking excel from "performing a suspicious action".
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
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
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!
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")
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
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
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
Can you share this code
Please check this post: excelmacroclass.blogspot.com/2021/12/send-email-from-excel.html
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
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.
Kelly Roland was close
Can i get this code
Sure, the code is here: 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..."
Good Stuff :)
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
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)
I have run time error 438
You may have mistyped or not properly defined some object, the mail object probably. Check the right code here: excelmacroclass.blogspot.com/2021/12/send-email-from-excel.html
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)
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
Please share the vba coding
VBA code is here: excelmacroclass.blogspot.com/2021/12/send-email-from-excel.html
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
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..."