Dear Readers,
In this article, we are going to learn how to send an email automatically by Excel Excel VBA. While executing the Excel Macro, sometimes you may need to send some Details or Results directly to your email ID or any other ID using Excel Macro. If you have some report or details which you want to send in a mail without log-in to your email account.
This you are going to achieve by using CDO Object.
What is CDO (Collaboration Data Objects)??
.
This Object is basically used for Active Messaging, Enables user to Access Global Server Objects and Address List For more about this read this Microsoft Help.
In Microsoft Excel, to use this Object you need to Add CDO Reference.
Add CDO Reference
Note: Without adding this reference you can Run the below Code. For running this Code, you can use below line to create Object runtime:
Set myMail = CreateObject("CDO.Message")
In this article, i will explain you 2 simple methods, to send an email by Excel VBA.
1. Excel VBA: Send email from Gmail
Sub SendEmailUsingGmail()
On Error GoTo Err
Dim NewMail As Object
Dim mailConfig As Object
Dim fields As Variant
Dim msConfigURL As String
Set NewMail = CreateObject("CDO.Message")
Set mailConfig = CreateObject("CDO.Configuration")
' load all default configurations
mailConfig.Load -1
Set fields = mailConfig.fields
'Set All Email Properties
With NewMail
.Subject = "Test Mail from LearnExcelMacro.com"
.From = "email@gmail.com"
.To = "email2@gmail.com;email3@gmail.com"
.CC = "email4@gmail.com"
.BCC = ""
.textbody = ""
End With
msConfigURL = "http://schemas.microsoft.com/cdo/configuration"
With fields
'Enable SSL Authentication
.Item(msConfigURL & "/smtpusessl") = True
'Make SMTP authentication Enabled=true (1)
.Item(msConfigURL & "/smtpauthenticate") = 1
'Set the SMTP server and port Details
'To get these details you can get on Settings Page of your Gmail Account
.Item(msConfigURL & "/smtpserver") = "smtp.gmail.com"
.Item(msConfigURL & "/smtpserverport") = 465
.Item(msConfigURL & "/sendusing") = 2
'Set your credentials of your Gmail Account
.Item(msConfigURL & "/sendusername") = "email@gmail.com"
.Item(msConfigURL & "/sendpassword") = "********"
'Update the configuration fields
.Update
End With
NewMail.Configuration = mailConfig
NewMail.Send
MsgBox ("Mail has been Sent")
Exit_Err:
Set NewMail = Nothing
Set mailConfig = Nothing
End
Err:
Select Case Err.Number
Case -2147220973 'Could be because of Internet Connection
MsgBox " Could be no Internet Connection !! -- " & Err.Description
Case -2147220975 'Incorrect credentials User ID or password
MsgBox "Incorrect Credentials !! -- " & Err.Description
Case Else 'Rest other errors
MsgBox "Error occured while sending the email !! -- " & Err.Description
End Select
Resume Exit_Err
End Sub
2. Excel VBA: Send email from Yahoo
Sub SendEmailUsingYahoo()
On Error GoTo Err
Dim NewMail As Object
Dim mailConfig As Object
Dim fields As Variant
Dim msConfigURL As String
Set NewMail = CreateObject("CDO.Message")
Set mailConfig = CreateObject("CDO.Configuration")
' load all default configurations
mailConfig.Load -1
Set fields = mailConfig.fields
'Set All Email Properties
With NewMail
.Subject = "Test Mail from LearnExcelMacro.com"
.From = "email@yahoo.co.in"
.To = "email1@gmail.com;email2@yahoo.com"
.CC = "email@email.com"
.BCC = ""
.textbody = ""
End With
msConfigURL = "http://schemas.microsoft.com/cdo/configuration"
With fields
'Enable SSL Authentication
.Item(msConfigURL & "/smtpusessl") = True
'Make SMTP authentication Enabled=true (1)
.Item(msConfigURL & "/smtpauthenticate") = 1
'Set the SMTP server and port Details
'To get these details you can get on Settings Page of your yahoo Account
.Item(msConfigURL & "/smtpserver") = "smtp.mail.yahoo.com"
.Item(msConfigURL & "/smtpserverport") = 465
.Item(msConfigURL & "/sendusing") = 2
'Set your credentials of your yahoo Account
.Item(msConfigURL & "/sendusername") = "email@yahoo.co.in"
.Item(msConfigURL & "/sendpassword") = "******"
'Update the configuration fields
.Update
End With
NewMail.Configuration = mailConfig
NewMail.Send
MsgBox ("Mail has been Sent")
Exit_Err:
Set NewMail = Nothing
Set mailConfig = Nothing
End
Err:
Select Case Err.Number
Case -2147220973 'Could be because of Internet Connection
MsgBox " Could be no Internet Connection !! -- " & Err.Description
Case -2147220975 'Incorrect credentials User ID or password
MsgBox "Incorrect Credentials !! -- " & Err.Description
Case Else 'Rest other errors
MsgBox "Error occured while sending the email !! -- " & Err.Description
End Select
Resume Exit_Err
End Sub
Excel VBA to Send Email with HTML Body
For sending HTML Body you just need to Change the Email Properties which you are setting above
With NewMail
.Subject = "Test Mail from LearnExcelMacro.com"
.From = "email@yahoo.com"
.To = "email2@gmail.com;email3@yahoo.com"
.CC = "email@email.com"
.BCC = ""
.HTMLBody = "Write your complete HTML Page"
End With
Excel VBA to Send Email with an Attachment
With NewMail
.Subject = "Test Mail from LearnExcelMacro.com"
.From = "vishwamitra01@yahoo.com"
.To = "vishwamitra02@gmail.com;info@learnexcelmacro.com"
.CC = "vishwamitra01@gmail.com"
.BCC = ""
.HTMLBody = "Write your complete HTML Page"
' For multiple Attachment you can add below lines as many times
.AddAttachment "C:\ExcelMacro-help.xls"
.AddAttachment "C:\ExcelMacro-help2.xls"
End With
Hi. The code sending email from yahoo is not work.
Error message : Object required (Error 424)
Please help.
Thanks in advance.
Hi Kakada,
Can you please copy paste your code here so that i can see or can you send it to Info@learnexcelmacro.com. I will fix it and send it to you.
Thanks,
Vish
hi mishra,,
please help my code.
Sub CDO_Mail_Small_Text_2()
Dim iMsg As Object
Dim iConf As Object
Dim strbody As String
Dim Flds As Variant
Set iMsg = CreateObject(“CDO.Message”)
Set iConf = CreateObject(“CDO.Configuration”)
iConf.Load -1 ‘ CDO Source Defaults
Set Flds = iConf.Fields
With Flds
.Item(“http://schemas.microsoft.com/cdo/configuration/smtpusessl”) = True
.Item(“http://schemas.microsoft.com/cdo/configuration/smtpauthenticate”) = 1
.Item(“http://schemas.microsoft.com/cdo/configuration/sendusername”) = “xxxx@gmail.com”
.Item(“http://schemas.microsoft.com/cdo/configuration/sendpassword”) = “********”
.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserver”) = “smtp.gmail.com”
.Item(“http://schemas.microsoft.com/cdo/configuration/sendusing”) = 2
.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = 25
.Update
End With
strbody = “Hi there” & vbNewLine & vbNewLine & _
“This is line 1” & vbNewLine & _
“This is line 2” & vbNewLine & _
“This is line 3” & vbNewLine & _
“This is line 4”
With iMsg
Set .Configuration = iConf
.To = “xxxx@gmail.com”
.CC = “”
.BCC = “”
.From = “xxxx@gmail.com”
.Subject = “Important message”
.TextBody = strbody
.Send
End With
End Sub
Will this code be working with “webmail.sbilife.co”.in or different codes are required for sending mail through webmail.sbilife.co.in. Can you please help.
Thank Vishwamitra Mishra for your reply.
My sending yahoo email code is copied from above code,
and now I have find the solution.
I have to change myMail to NewMail for three lines.
myMail.Configuration.Fields.Item _ 'NewMail.Configuration…..
("http://schemas.microsoft.com/cdo/configuration/smtpserver"😉 = "smtp.mail.yahoo.com"
myMail.Configuration.Fields.Item _ 'NewMail.Configuration…..
("http://schemas.microsoft.com/cdo/configuration/smtpserverport"😉 = 465
myMail.Configuration.Fields.Item _ 'NewMail.Configuration…..
("http://schemas.microsoft.com/cdo/configuration/sendusing"😉 = 2
So is it working now?
Yes, it is working now. Thanks.
You are Welcome 🙂
Hi, I need some help. I trying to first convert a excel spreadsheet to pdf and send it as an attachment via gmail. Please help!
Sub sendReminderMail()
ChDir “C:\Users\mbarboza\Desktop\KW Reports”
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Sheet1.Range(“P1”), _
OpenAfterPublish:=True
Dim myMail As Object
Dim mailconfig As Object
Dim fields As Variant
Dim msconfigURL As String
Set myMail = CreateObject(“CDO.Message”)
Set mailconfig = CreateObject(“CDO.Configuration”)
myMail.Configuration.fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpusessl”) = True
myMail.Configuration.fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpauthenticate”) = 1
myMail.Configuration.fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserver”) = “smtp.gmail.com”
myMail.Configuration.fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = 465
myMail.Configuration.fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendusing”) = 2
myMail.Configuration.fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendusername”) = “mtbarboza1@gmail.com”
myMail.Configuration.fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendpassword”) = “*******”
myMail.Configuration.fields.Update
With myMail
.Subject = “Test Email from Marcos”
.From = “mtbarboza1@gmail.com”
.To = “mbarboza@miamiherald.com”
.CC = “”
.BCC = “”
.TextBody = “”
End With
On Error Resume Next
myMail.Send
MsgBox (“Mail has been Sent”)
Set myMail = Nothing
End Sub
Hi,
Thanks for your VBA code.
It also worked on my computer but unfortunately when I send an email, even if the email is well received and sent, it doesn't go to my sent items folder. (for a Gmail and Yahoo account)
Do you know why?
I would to keep tracks of the email sent.
Thanks
Julien
Hi Julien,
It is not possible, that mail will not be there in the sent items. Can you please check you code again and see if you are checking the sent items of the same account which you have mentioned in the code.
I would like to see your code, can you copy paste here?
Thanks,
Vish
Thanks Vish for your answer.
In fact it worked for Gmail but not for Yahoo (I checked my yahoo settings and I checked the box to have copy of my emails sent to my sent folder
This is my code (it's yours with some modification)
Public Function SendEmailUsingOther(strSendTo As String, strSubject As String, MailType As String, fld As String, Optional tempfile, Optional attch1, Optional attch2, Optional attch3)
MailSent = False
Dim NewMail As CDO.Message
Set NewMail = New CDO.Message
'Set myMail = CreateObject("CDO.Message")
'Enable SSL Authentication
NewMail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpusessl"😉 = True
'Make SMTP authentication Enabled=true (1)
NewMail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate"😉 = 1
'Set the SMTP server and port Details
'To get these details you can get on Settings Page of your Gmail Account
Select Case MailType
Case "yahoo"
NewMail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport"😉 = 465
NewMail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver"😉 = "smtp.mail.yahoo.com"
Case "gmail"
NewMail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport"😉 = 465
NewMail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver"😉 = "smtp.gmail.com"
End Select
NewMail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing"😉 = 2
'Set your credentials of your Gmail Account
SenderEmail = InputBox("Please enter your email address")
NewMail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusername"😉 = SenderEmail
NewMail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendpassword"😉 = InputBox("Please enter your password")
'Update the configuration fields
NewMail.Configuration.Fields.Update
NewMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl"😉 = "true"
'Set All Email Properties
'##########################""
'Open the HTML file using the FilesystemObject into a TextStream object
Set FSObj = New Scripting.FileSystemObject
Set TStream = FSObj.OpenTextFile(fld, ForReading)
'Now set the HTMLBody property of the message to the text contained in the TextStream object
strHTML = TStream.ReadAll
'################################
With NewMail
.subject = strSubject
.From = SenderEmail
.To = strSendTo
'.CC = ""
.BCC = ""
'.TextBody = ""
.HTMLBody = strHTML
If tempfile "" Then
.AddAttachment tempfile
End If
If Trim(attch1) "" Then
.AddAttachment attch1
End If
If Trim(attch2) "" Then
.AddAttachment attch2
End If
If Trim(attch3) "" Then
.AddAttachment attch3
End If
End With
'MsgBox (strHTML)
NewMail.Send
'MsgBox ("Mail has been Sent")
MailSent = True
pbsend:
If MailSent = False Then
MsgBox ("Due to an issue (password, email address,attachments…), the email hasn't been sent")
End If
'Set the NewMail Variable to Nothing
Set NewMail = Nothing
End Function
Thanks for your help
Thanks Julien,
I will get back to you shortly 🙂
Thanks,
Vishwa
Hi Vish.
I am so sorry to bother you but I have a code problem that I have been trying to solve for 3 weeks. I am self taught in VBA but I am not good enough to figure this out. I have written CDO code for sending emails for both comcast and gmail and they work fine but when I try to apply it to yahoo it does not work. I have tried asking on mrexcel but they could not help and many other people. None could tell me what is wrong. I then created a new email at yahoo with a new password. That didn’t work. PLEASE take the time to look at this. I haven’t got anyone else to turn to.
What I want to do is send cells A5″:W28 in an excel worksheet. I THINK if I get your code to work I MAY be able to change it enough to send the cells i need as a PDF. The people I send to do not have Excel or outlook.
I have the latest Excel and running windows 10.
Below is the code I copied and I replaced the places that needed it with my information
My address and password are in it so you can try it like i do.
That is a made up address so you I could try the code.
My E-Mail address is D1C@comcast.net.
Sub SendEmailUsingYahoo()
On Error GoTo Err
Dim NewMail As Object
Dim mailConfig As Object
Dim fields As Variant
Dim msConfigURL As String
Set NewMail = CreateObject(“CDO.Message”)
Set mailConfig = CreateObject(“CDO.Configuration”)
‘ load all default configurations
mailConfig.Load -1
Set fields = mailConfig.fields
‘Set All Email Properties
With NewMail
.Subject = “Test Mail from LearnExcelMacro.com”
.From = “testncaa@yahoo.com” ‘ Changed
.To = “D1C@comcast.net” ‘Changed
.CC = “”
.BCC = “”
.TextBody = “This is a test”
End With
msConfigURL = “http://schemas.microsoft.com/cdo/configuration”
With fields
‘Enable SSL Authentication
.Item(msConfigURL & “/smtpusessl”) = True
‘Make SMTP authentication Enabled=true (1)
.Item(msConfigURL & “/smtpauthenticate”) = 1
‘Set the SMTP server and port Details
‘To get these details you can get on Settings Page of your yahoo Account
.Item(“http://schemas.Microsoft.Com/cdo/configuration/smtpusetls”) = True
.Item(msConfigURL & “/smtpserver”) = “smtp.mail.yahoo.com”
.Item(msConfigURL & “/smtpserverport”) = 587
.Item(msConfigURL & “/sendusing”) = 2
‘Set your credentials of your yahoo Account
.Item(msConfigURL & “/sendusername”) = “testncaa@yahoo.com” ‘Changed
.Item(msConfigURL & “/sendpassword”) = “martin105*” ‘Changed
‘Update the configuration fields
.Update
End With
NewMail.Configuration = mailConfig
NewMail.Send
MsgBox (“Mail has been Sent”)
Exit_Err:
Set NewMail = Nothing
Set mailConfig = Nothing
End
Err:
Select Case Err.NUMBER
Case -2147220973 ‘Could be because of Internet Connection
MsgBox ” Could be no Internet Connection !! — ” & Err.Description
Case -2147220975 ‘Incorrect credentials User ID or password
MsgBox “Incorrect Credentials !! — ” & Err.Description
Case Else ‘Rest other errors
MsgBox “Error occured while sending the email !! — ” & Err.Description
End Select
Resume Exit_Err
End Sub
This is the Err I got.
Case -2147220973 ‘Could be because of Internet Connection
MsgBox ” Could be no Internet Connection !! — ” & Err.Description
Thank you so much
Marty
Hi,
I just need to send an similar details for more the 50 members only amount will be changed…. it will take more time to send an email to each and every person.
Can you please help me on this…… is there any ways to creat a macro for this problem…
Thanks,
Murali
Hi Murali,
Yes, it's easy to do the same. You need to put the above code in a loop and keep sending the email by taking the email Address and corresponding Subject and Body of the email.
One Sample Excel is sent to your email ID. You can customize the same for your requirement. If need any help, let me know.
Thanks,
Vishwa
This is a great example. Just one question. I have a module in Excel that will attach a workbook and send an email through gmail. When the email arrives the file is correpted and [WARNING: A/V UNSCANNABLE] is in the message header. Any ideas?
Hi Amanda,
Thanks for writing.
I tried with an example and i did not face this issue. Would you please share your code, so that i can try to re-produce this issue.
Thanks,
Vish
I'm trying to get this to work but am unable to. With the posted code, I'm getting an error –
"Run-time error '-2147220973 (80040213)':
The transport failed to connect to the server."
I double checked my gmail credentials. I also tried smtpserverport = 465 but still get the same error. Are there other settings that I can add or change?
Hi
I was looking mostly for this! First of all thank you very much!
I also have a question!
Can this be adopted to receive delivery notifications?(like return receipts)
Any help will be appreciated!
Hi Sanjay,
Thanks alot for the feedback.
I am not sure about the delivery notification. I will try and if i get anything, i will let you know.
Keep giving your valuable feedback.
Vish
Hello there! I need some help with macro for sending e-mails.
I use 2 email addresses at work. One is Microsoft Exchange Server and the other one is my work inbox. I need my code to take the work inbox to send e-mails, since i do not have access to send external emails using microsoft exchange server. is there a way outlook can choose the inbox from the drop down menu and send e-mails? i am using this at the moment
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = "e-mail addresses"
.CC = ""
.BCC = ""
.Subject = ""
.Body = ""
.Body = ""
.Body = ""
.Send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Hi,
You can create two emails and while sending the email use the below code.
Instead of writing just .Send use the below line:
.SendUsingAccount = OutApp.Session.Accounts.Item(1)
Where item(1), item(2), .. etc will be your accounts which are configured in your outlook.
Try it and let me know, if it helps.
Thanks,
Vishwa
Hi
When I run the code to gmail i get the error:
Run-time error '-2147220973(80040213)'
The transport failed to connect to server.
Do you know how to fix that?
Hi Andre,
I have sent an email to your email id please check.
Thanks,
Vish
Hi
I also get this error when I run the code to gmail :
Run-time error ‘-2147220973(80040213)’
The transport failed to connect to server.
Could you advise, thanks in advance! 🙂
Hi Hoys,
This error usually comes when you are trying send email from a network where GMAIL is blocked. Please check if gmail is not blocked in your network.
Thanks,
Vish
Hi,
Thank you for this code. I am getting the same error:
Run-time error ‘-2147220973(80040213)’
The transport failed to connect to server.
I am working from home and gmail should not be blocked on my network. How do I check that? Any other way I could fix this.
Thanks in advance.
Its working! Just had to use a different port. Thanks!!
Hi Charles,
Did you use 465 or 587?
For me its working at 25 itself.
Thanks
Port 587. Thanks Vish.
Thanks Charles for this solution.
Hi friends, if you also facing the same error, then can try using Port as 587, instead of 25.
Thanks again Charles.
Any luck with this error? I get it when trying the gmail and yahoo code:
Run-time error ‘-2147220973(80040213)’
The transport failed to connect to server.
Hi,
I want to create an e-mail draft in gmail by using VBA with Excel, so that I can modify the body of the e-mail before sending it.
Is this possible? Can you help me?
Thanks a lot!
Hi Ramir,
For CDO.Message it is not possible to display before sending it. You can just send it. Whatever you want to modify, you can modify in the code or in excel before sending it.
not working in my gmail account
Try using the Port as 587, instead of 25.
I am trying to send mails using macro via outlook. The macro works fine in my system, but the same throws security prompt error in outlook if sent from my colleague id? why is outlook showing security prompt saying…to allow or deny…? how should this be resolved. Is this an excel macro or outlook issue?
Looks like it is your Outlook security issue.
One question. I have a module in Excel that will attach a workbook and send an email through gmail. When the email arrives the file is message and excel found unreadable content in 'FILE'. want to recover the contents of this workbook? If you trust the source of this workbook, click Yes.
When I try to open a file appears blank.. Any ideas?
This worked the first time I tried it with my g-mail account,
I created another g-mail account and now I get this error message with both accounts:
Run-time error '2147220975 (80040211)
The message could not be sent to the SMPT server. The transport
error code was 0x80040217. The server response was not available.
Is it possible to ammend the code to use Hotmail?
Dear Vishwamitra,
I'm absolutely overjoyed to find this link. Quite informative I must admit.
I was wondering if you could help me with a problem that I've been stuck with. To my knowledge CDO method for Gmail sends the emails directly without giving an opportunity to review it before clicking the send button. Is there a way I can create Gmail drafts with attachments, but preview them before sending.
I have a VBA code but the problem is that it doesn't allow a preview, which is extremely critical. The irony is that I can't use Outlook as our company is on Google mail.
Look forward to your suggestions / inputs.
Happy to provide more info!
Thanks,
Gyan
Hi,
Did you ever get a response about this? I have the same issue; I need to sent emails using vba (excel) but my company is on Google e-mail.
I tried the code with my personal e-mail acconut and it works; however when I thry using my companies e-mail, it doesn´t (myname@mycompany.com -> on google).
thanks,
Hi,
I’m dealing with same problem you wrote back on 2013.
did you able to find a way to make preview before sanding the email by Gmail (via VBA code) ?
In case you did, I’ll be happy to know how ?
Thanks !
Hi,
There is no built-in option provided by google to see a preview before sending the email by gmail, yahoo etc. If you need to have a preview of the email before sending, that can be built in excel using Excel UserForm which will look like gmail email draft screen.
Does this work in outlook? I t would be helpful if the same is there for outlook.
Hi Vishwa, I still have this error, even trying 25, 465, and 587
Run-time error ‘-2147220973(80040213)’
The transport failed to connect to server.
But now i started to understand ,probably it is because our network is protected with proxy server w/c if uncheck will disconnect the network. I know of your vast knowledge on this and hope your thought for ways to address this. more power! Warlito
I run the code but getting an error : the SMTP server name is required.and was not
found in configuration source
Is there a way to have the code prompt the Excel user for a username and password?
Is there a way to have the user send a range of cells? Or a selection of cells?
This is an awesome article! Thanks so much. 🙂
Hello,
I want to attach only (worksheetsheet1),and send from gmail;
Clearor reset (worksheetsheet1) after to be ready to send a newmail with new numbersdata
Thanks in advance
Hi Orion,
Find the below article. It will help you. Before attaching make sure that you put one statement to clear the content. http://learnexcelmacro.com/wp/2012/08/mail-on…
Hi Mishra,
thanks for the suggestion, but i wont to send Activesheet from Gmail, not from Outlook. Can you give me a solution please?
Hi Orion,
Concept will remain same. Just use the same concept for sending it through Gmail. if you are still not clear how to use it, send me an email. I will reply you with the working code.
Regards,
Vishwa
Hi,
I will love to have that code as well.
Thanks
Francisco
Hi dear, I need your help. I have used the above codes to send an email through gmail. I have used the semi columns to add 5 CC and 5 BCC. The only issue is when I do not use all 5 CCs or BCCs it gives an error. The emails for CCs and BCCs are found in a range of cells. How can I have the option of 5 CCs and 5 BCCs but be able to use one, two or none of them ?
Thanks in advance
ಹಲೋ, Mishra!
I'm sending emails using a VBA macro with Excel and Outlook2010.
But for gmails-yahoo-hotmail-etc users it is not working well. The image become an attached file and the email content is “translated” into codes (alphanumeric).
Outlook users open it adequately, they can see the image.
Do you know how to solve it? Where I’m doing it wrong?
ವಂದನೆ
=====+=====+=====+=====+=====+========+===+
Sub enviar_email()
'CustomerAddress = "Vagner.duarte@estacio.br"
CustomerAddress = "vagner@vagnerduarte.com"
For i = 2 To 50000
If (Sheets("Dados").Range("A" & i) = "") Then
Exit For
Else
nome = Sheets("Dados").Range("A" & i)
Sheets("E-mail").Select
ActiveSheet.Shapes.Range(Array("img_pascoa")).Select
'Selection.Copy
ActiveWorkbook.EnvelopeVisible = True
With ActiveSheet.MailEnvelope
.Item.To = Sheets("Dados").Range("A" & i)
.Item.Subject = nome & " deseja um Feliz Páscoa!"
.Item.send
End With
End If
Sheets("Dados").Select
Range("A1").Select
Next
MsgBox "E-mail(s) enviado com sucesso!"
End Sub
=====+=====+=====+=====+=====+========+===+
It consists in two columns: 'DE' is the variable data that will be shown in the subjet (the name of the person). The other column is the account that the email will be sent.
I keep getting a user defined error message and it highlights the sub name, i tried to define it for hotmail but not sure if it works
PLEASE HELP
Sub NewMail()
Dim NewMail As CDO.Message
Set NewMail = New CDO.Message
‘Enable SSL Authentication
NewMail.Configuration.Fields.Item _
(“http://login.live.com/cdo/configuration/smtpusessl”) = True
‘Make SMTP authentication Enabled=true (1)
NewMail.Configuration.Fields.Item _
(“http://login.live.com/cdo/configuration/smtpauthenticate”) = 1
‘Set the SMTP server and port Details
‘To get these details you can get on Settings Page of your Gmail Account
NewMail.Configuration.Fields.Item _
(“http://login.live.com/cdo/configuration/smtpserver”) = “smtp.live.com”
NewMail.Configuration.Fields.Item _
(“http://login.live.com/cdo/configuration/smtpserverport”) = 25
NewMail.Configuration.Fields.Item _
(“http://login.live.com/cdo/configuration/sendusing”) = 2
‘Set your credentials of your Gmail Account
NewMail.Configuration.Fields.Item _
(“http://login.live.com/cdo/configuration/sendusername”) = “**********@hotmail.co.za”
NewMail.Configuration.Fields.Item _
(“http://login.live.com/cdo/configuration/sendpassword”) = “*********”
‘Update the configuration fields
NewMail.Configuration.Fields.Update
‘Set All Email Properties
With NewMail
.Subject = “Test Mail”
.From = “***********@hotmail.co.za”
.To = “**********@****.com”
.CC = “”
.BCC = “”
.textbody = “”
End With
NewMail.Send
MsgBox (“Mail has been Sent”)
‘Set the NewMail Variable to Nothing
Set NewMail = Nothing
End Sub
Sir,
I was looking how to send birthday e-mail automatically from excel through yahoo.I had once sent by using MS outlook but now it is not functioning.I came across your website (and your name) and thankfully I believe you will help me to solve problem. I used the following macro:
Sub SendBirthdayWishes()
Dim OL As Object, olMail As Object, blnOpened As Boolean
Dim ws As Worksheet, c As Range, i As Long, j As Long
Dim k As Long, arrData(1 To 1000, 1 To 3) As Variant
Dim sBody As String, objwShell As Object
Application.ScreenUpdating = False
Set ws = Sheets(“Sheet1”)
‘Set ws = ThisWorkbook.Worksheets(“Sheet1”)
For Each c In ws.Range(“C2:C” & ws.Cells(ws.Rows.Count, 3).End(xlUp).Row)
‘If Month(c.Value) = Month(VBA.Date()) And Day(c.Value) = Day(VBA.Date()) Then
If Month(c.Value) = Month(VBA.Date()) Then
i = i + 1
arrData(i, 1) = c.Offset(0, -2).Value
arrData(i, 2) = c.Offset(0, -1).Value
arrData(i, 3) = c.Value
‘ c.Offset(0, 1) = arrData(i, 3)
End If
Next c
If IsEmpty(arrData(1, 1)) Then Exit Sub
On Error Resume Next
Set objwShell = CreateObject(“wscript.shell”)
objwShell.Run (“””C:Program FilesExpress ClickYesClickYes.exe”” -activate”)
Set OL = GetObject(, “Outlook.Application”)
If OL Is Nothing Then
Set OL = CreateObject(“Outlook.Application”)
blnOpened = True
End If
On Error GoTo 0
For j = LBound(arrData) To UBound(arrData)
If IsEmpty(arrData(j, 1)) Then Exit For
Set olMail = OL.CreateItem(0)
olMail.To = arrData(j, 2)
For k = LBound(arrData) To UBound(arrData)
If IsEmpty(arrData(k, 1)) Then Exit For
olMail.Cc = arrData(k, 2) & “; ”
Next k
olMail.Subject = “Happy Birthday!”
sBody = “Just wanted to wish you a happy birthday when you turn ”
sBody = sBody & Year(arrData(j, 3)) – Year(VBA.Date()) & “!”
sBody = sBody & vbNewLine & vbNewLine
sBody = sBody & “JASKARAN MANJIT SIMRAN HARNOOR-JMSH” ‘Signature line
olMail.Body = sBody
olMail.Send
‘c.Offset(0, 2) = sBody
Next j
objwShell.Run (“””C:Program FilesExpress ClickYesClickYes.exe”” -stop”)
ThisWorkbook.Saved = True
Application.ScreenUpdating = True
End Sub
Please help me to modify the above macro to send birhtday wishes through YAHOO INSTEAD OF OUTLOOK
I want to receive a email before a long list with date will expire with 3 day…its posibile? if yes please explain me how can I do it.
thanks
muchisimas gracias por su codigo
se los agradezco bastante
Hi Kakada
My company is using google to send emails. I have my official email id as amit
*******.com
Can you please send me the VBA to send emails from official email id from excel.
Regards
Amit Sharma
Regards
Amit Sharma
hi, i dont know how to send birthday wishes through ms excel and also how to use ms outlook. kindly tell me inwhich way to use it. clearly
Hi,
I tried with all the ports i.e. 25, 465 ,587 but the error is coming.
please help.
Thanks
Hi,
I tried with all the ports for gmail i.e. 25, 465 ,587 but the error is coming.
kindly help me,its urgent.
Thanks
All,
Several people were having some issue with outlook. Here is the VBA I set up for use with MS Outlook 2010
Private Declare Function ShellExecute Lib “shell32.dll” _
Alias “ShellExecuteA” (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
Sub SendEMail()
Dim Email As String, Subj As String
Dim Msg As String, URL As String
Dim r As Integer, x As Double
For r = 2 To 7 ‘data in rows 2-7
‘ Get the email address
Email = Cells(r, 3)
‘ Message subject
Subj = “”
‘ Compose the message
Msg = vbNewLine & “Dear ” & Cells(r, 1) & “,” & vbNewLine & vbNewLine
Msg = Msg & “”
‘ Replace spaces with %20 (hex)
Subj = Application.WorksheetFunction.Substitute(Subj, ” “, “%20″)
Msg = Application.WorksheetFunction.Substitute(Msg, ” “, “%20”)
‘ Replace carriage returns with %0D%0A (hex)
Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, “%0D%0A”) ‘ Create the URL
URL = “mailto:” & Email & “?subject=” & Subj & “&body=” & Msg
‘ Execute the URL (start the email client)
ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus
‘ Wait two seconds before sending keystrokes
Application.Wait (Now + TimeValue(“0:00:05”))
Application.SendKeys “{Tab}{Tab}{Tab}{Tab}{Tab}^{End}{Return}{Return}^v”
Application.SendKeys “%s”
Next r
End Sub
Private Sub Workbook_Open()
End Sub
IM trying to do it from a service account and it is setting my default personal email
Hi!
I’m also getting Run-time eror and i’ve tried all three ports. Can you please provide an answer for this? It seems like multiple people are also having a lot of trouble
Hi Ben,
I have fixed the code a bit now. Can you copy the latest code from the post and try it? Provide your feedback if it works for you.
Please my colleague use to send a single message to multiple recipients using vba excel, I asked but he refuse to help
I am to send a message to multiple recipients with different attchment to different reciever. please help me out with the Macros.
thanks
.From is not working. It is not taking the respective mail id mentioned
Hi all,
I am trying to use excel to send out email and also having the same excel as attachment. I am using yahoo to send out the mail and the attachment code is: ‘.Attachments.Add ActiveWorkbook.FullName but it say that it is send out an active worksheet, how can i resolve this?
Many thanks.
Hi Adrian,
The best and safe solution is to first save the your current workbook which you want to send it in a different location (may be in temp folder), attach the file from the temp location and send it. refrain from using the same workbook as an attachment while sending out an email.
Let me know if this helps.
refer the below article:
http://learnexcelmacro.com/wp/2012/01/how-to-send-activeworkbook-as-attachment-in-email/
Hi, First of all, Thank you for sharing this code.
I set up the code as per yours. But the email is not being received. When I run the code it states its running and then displays the “Mail has been sent” message. But when I check the “to” email – nothing has been received. I’m not sure where the issue is.
here is my code,
Sub send_email_via_gmail()
Dim myMail As CDO.Message
Set myMail = New CDO.Message
myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpusessl”) = True
myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpauthenticate”) = 1
myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserver”) = “smtp.gmail.com”
myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = 25
myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendusing”) = 2
myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendusername”) = “xxxx@gmail.com”
myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendpassword”) = “xxxx”
myMail.Configuration.Fields.Update
With myMail
.Subject = “Test Email”
.From = “xxxx@gmail.com”
.To = “xxxxx@gmail.com”
.TextBody = “G’Day”
.AddAttachment “C:test.xlsx”
End With
On Error Resume Next
myMail.Send
MsgBox (“Mail has been sent”)
Set myMail = Nothing
End Sub
Would it be possible to see why the code is not doing what it suppose to. I note I do not receive any errors when executing the code.
Thanks,
Winston
Dear Sir
I HAVE DATA OF EMPLOYEES IN EXCEL SHEET WITH EMAIL ID , I JUST WANT THAT EXCEL SENT THE EMAIL WHEN I CLICK ON BUTTON TO ALL EMAIL ID OR SELECTIVE EMAIL ID WITH FETCHING RESPECTIVE EMPLOYEE DATA .
HOPING A FAVORABLE REPLY FROM YOUR END.
Dear Sir , can you provide me the code for sending email in excel thorough lotus notes having email id in a column of excel sheet after fetching data of a particular employee if I click on a particular send button in excel to a specified email id .
waiting for a prompt and reliable reply from your end with a suitable explaining example .
ok..I have a basic knowledge about excel and am trying to learn new things…need some help with this..
Example:I have made a Time sheet in excel for John..as John is not tech with IT….in this time sheet I have added details,login,logout,formulas for calculating the total hours etc…so when John receives it he just have to put the login and logout timings and the remaining is calculated automatically…
so here is my question,
1)can i email this active workbook as a body of the email(Not as attachment) to John??
2)And when John receives the email he should be able to fill the sheets with timings in the active worksheet in the email itself and forward me back the same email.(No need to download,fill,attachment and send back headache for John)
3)which mail supports this….I use outlook 2007…
so I was just wondering is there any thing like this and Detailed Answer is much appreciated…Thanks in advance
Hi am getting the following system error
&H80040211(-2147220975)
pls help
regards,
Manikandan
Hi,
I’m trying to send mail notification through Gmail however when i use one recipient it works but it gives error message when I try the same with multiple.
Error – run-time -2147220975 (80040211)’:
The Message cannot be sent to the SMTP server. The transport error code was 0x80040217. The Server response was not available.
code-
Sub email()
Dim NewMail As CDO.Message
Set NewMail = New CDO.Message
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/smtpusessl”) = True
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/smtpauthenticate”) = 1
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/smtpserver”) = “smtp.gmail.com”
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = 25
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/sendusing”) = 2
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/sendusername”) = Sheets(“Mail Format”).Range(“F14″).Value ‘”*****”
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/sendpassword”) = Sheets(“Mail Format”).Range(“L13″).Value ‘”****”
NewMail.Configuration.Fields.Update
With NewMail
.Subject = Sheets(“Mail Format”).Range(“f14″).Value ‘”Test Mail”
.From = Sheets(“Mail Format”).Range(“F13”).Value
.To = Sheets(“Mail Format”).Range(“F14”).Value
‘.CC = Sheets(“Mail Format”).Range(“F15”).Value
‘.BCC = “”
.TextBody = Sheets(“Mail Format”).Range(“B16″).Value ‘”Payment Proposal is created”
End With
NewMail.Send
MsgBox (“Mail has been Sent”)
Set NewMail = Nothing
End Sub
change the port to 465, it worked to me
Error – run-time -2147220975 (80040211)’:
The Message cannot be sent to the SMTP server. The transport error code was 0x80040217. The Server response was not available.
hi,
I have the same error. Were you able to solve it?
Hi everybody:
This worked perfecty fine to me. The number of the port was the trouble, you have to change the email address and password in this example.
Sub SendEmailUsingGmail()
Dim NewMail As CDO.Message
Set NewMail = New CDO.Message
‘Enable SSL Authentication
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/smtpusessl”) = True
‘Make SMTP authentication Enabled=true (1)
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/smtpauthenticate”) = 1
‘Set the SMTP server and port Details
‘To get these details you can get on Settings Page of your Gmail Account
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/smtpserver”) = “smtp.gmail.com”
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = 465
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/sendusing”) = 2
‘Set your credentials of your Gmail Account
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/sendusername”) = “mail_from”
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/sendpassword”) = “password”
‘Update the configuration fields
NewMail.Configuration.Fields.Update
‘Set All Email Properties
With NewMail
.Subject = “Test Mail from LearnExcelMacro.com”
.From = “mail_from”
.To = “mail_to”
.CC = “”
.BCC = “”
.TextBody = “”
End With
NewMail.Send
MsgBox (“Mail has been Sent”)
‘Set the NewMail Variable to Nothing
Set NewMail = Nothing
End Sub
on first run only it is showing as user-defined type not available can you pls help
Sub SendEmailUsingGmail()
Dim NewMail As CDO.Message
Set NewMail = New CDO.Message
‘Enable SSL Authentication
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/smtpusessl”) = True
‘Make SMTP authentication Enabled=true (1)
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/smtpauthenticate”) = 1
‘Set the SMTP server and port Details
‘To get these details you can get on Settings Page of your Gmail Account
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/smtpserver”) = “smtp.gmail.com”
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = 465
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/sendusing”) = 2
‘Set your credentials of your Gmail Account
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/sendusername”) = “hmishra429@gmail.com”
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/sendpassword”) = “******”
‘Update the configuration fields
NewMail.Configuration.Fields.Update
‘Set All Email Properties
With NewMail
.Subject = “Progress report”
.From = “himanshu.mishra@gmail.com”
.To = “xxxx@gmail.com”
.CC = “xxxx@gmail.com”
.BCC = “”
.HTMLBody = “Dear Sir,Please find attached progress report.”
‘ For multiple Attachment you can add below lines as many times
.AddAttachment “\10.36.120.109Production Plant-1Combined Reports–Plant 1Oct-15Combined Project status-Final-Oct-15.xls”
End With
NewMail.Send
MsgBox (“Mail has been Sent”)
‘Set the NewMail Variable to Nothing
Set NewMail = Nothing
End Sub
Thank you very much
it works like a charm
(sorry for sending you a testmail)
Hi vish,
i tried sending email from excel using code above bet getting below error
Run Time Error ‘-2147220975 (80040211)’: Automation Error
Can you please help me to fix this
Hi Sir,
I am getting below error can you please help me in this?
Run-time error ‘-2147220975(80040211)’:
The message could not be sent to the SMTP server. The transport
error code was 0x80040217. The server response was not available
hello sir,
i try to send message my excel vba
but error message return to my account
Sub SendEmailUsingGmail()
Dim NewMail As CDO.Message
Set NewMail = New CDO.Message
‘Enable SSL Authentication
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/smtpusessl”) = True
‘Make SMTP authentication Enabled=true (1)
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/smtpauthenticate”) = 1
‘Set the SMTP server and port Details
‘To get these details you can get on Settings Page of your Gmail Account
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/smtpserver”) = “smtp.gmail.com”
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = 25
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/sendusing”) = 2
‘Set your credentials of your Gmail Account
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/sendusername”) = “alfalakandco@gmail.com”
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/sendpassword”) = “onlyforme”
‘Update the configuration fields
NewMail.Configuration.Fields.Update
‘Set All Email Properties
With NewMail
.Subject = “Test Mail from LearnExcelMacro.com”
.From = “alfalakandco@gmail.com”
.To = “lansuzy@gmail.com”
.CC = “”
.BCC = “”
.TextBody = “hello good afternoon”
End With
NewMail.Send
MsgBox (“Mail has been Sent”)
‘Set the NewMail Variable to Nothing
Set NewMail = Nothing
End Sub
==================================================================================
Delivery to the following recipient failed permanently:
lansuzy@gmail.com
Technical details of permanent failure:
Message rejected. See https://support.google.com/mail/answer/69585 for more information.
—– Original message —–
X-Received: by 10.194.24.65 with SMTP id s1mr3367292wjf.137.1460464773229;
Tue, 12 Apr 2016 05:39:33 -0700 (PDT)
Return-Path:
Received: from goodPC (WimaxUser3662-147.wateen.net. [110.86.72.148])
by smtp.gmail.com with ESMTPSA id 202sm3491285wmw.5.2016.04.12.05.39.30
(version=TLS1 cipher=ECDHE-RSA-AES128-SHA bits=128/128);
Tue, 12 Apr 2016 05:39:32 -0700 (PDT)
Thread-Topic: Test Mail from LearnExcelMacro.com
thread-index: AdGUuFuSP5UPU9YYQjmI46rFEnuUfA==
From:
To:
Cc:
Bcc:
Subject: Test Mail from LearnExcelMacro.com
Date: Tue, 12 Apr 2016 17:39:30 +0500
Message-ID:
MIME-Version: 1.0
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
X-Mailer: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.1.7601.17609
hello good afternoon
Hi. Thanks for your classy advice on CDO.
I have very serious issue while using CDO through VBS.
When I’m trying to send email through 172.**.***.123 where smtp access is provided, I’m able to send email.
But when same vbs file is placed on same *.123 server, While trying to run the file from any other location by sharing the file, I’m unable to send email.
Is there any solution for same. As in corporate, I cannot provide smtp access to every user machine who is going to use the file.
I every time spent my half an hour to read this web site’s posts everyday along with a mug of coffee.
Great post! Here’s a similar method for automating email notifications using Python: http://bit.ly/2b90i65
I tried running this but get the following error
Run-time error’-2147024893 (80070003)’:
Automation error
The system cannot find the path specified
***Here is my code****
Sub SendEmailUsingGmail()
Dim NewMail As CDO.Message
Set NewMail = New CDO.Message
‘Enable SSL Authentication
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/smtpusessl”) = True
‘Make SMTP authentication Enabled=true (1)
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/smtpauthenticate”) = 1
‘Set the SMTP server and port Details
‘To get these details you can get on Settings Page of your Gmail Account
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/smtpserver”) = “smtp.gmail.com”
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = 25
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/sendusing”) = 2
‘Set your credentials of your Gmail Account
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/sendusername”) = “investigations@psspfund.co.za”
NewMail.Configuration.Fields.Item _
(“http://schemas.microsoft.com/cdo/configuration/sendpassword”) = “************”
‘Update the configuration fields
NewMail.Configuration.Fields.Update
‘Set All Email Properties
With NewMail
.Subject = “Test Mail from LearnExcelMacro.com”
.From = “investigations@psspfund.co.za”
.To = “peter@viomedic.co.za;kelezibi@viomedic.co.za”
.CC = “tshepi@gmail.com”
.BCC = “”
.TextBody = “”
HTMLBody = “Write your complete HTML Page”
‘ For multiple Attachment you can add below lines as many times
.AddAttachment “C:desktopinsurance services.doc”
End With
NewMail.Send
MsgBox (“Mail has been Sent”)
‘Set the NewMail Variable to Nothing
Set NewMail = Nothing
End Sub
Hi,
When i am running the macro its asking me the VBA project password.
Request you to kindly Help vishwa.
Hi, to send an email, you do not need VBA password. Due to some error occurred, you are trying to debug the code and hence it was asking for the VBA password.
Hi ,i already read your posting. always good. BTW, can you please guide me how a macro can work in a web mail box inside citrix. thanks
Thanks BRO its working Superb 🙂
initially was getting automation error but after changing my accounts setting i was able to send messages. Cheers 🙂
What settings did you have to change?
How to send email from gmail using Excel VBA.
Note : Using get elements by ID.
hi i have tried with your code,code is working but i am getting error (run-time error 2147220975(80040211) the message could not be sent to the smtp server. the transport error code was 0x80040217.teh server response was not available).please respond me thanks
Is there any way of displaying the message before sending it via Gmail ??? If done through Outlook, Excel takes you to outlook email with attachment and you check/edit and just click “send”. I need the same but for Gmail.
I know that .Display does not work for CDO, but maybe there is a way of getting around/past that ???
Hi,
Right now I am creating a logging software using macro in WPS software. So my requirement is during logging a mail should be send to the person who takes the tool/instrument about the log. The problem with me is that I don’t have outlook app, but have an outlook mail id. So my macro should do the following – The macro should open firefox browser, then go to a outlook mail address. Since I have already opened my outlook mail, the macro should not have to sign in. The next step of macro is to create a new mail and fill all the necessary things and send the mail. Finally the newly opened tab should close after sending this mail. Is this possible using VBA. If possible can any one give me the code.
Thanks in advance,
Rahul Mohan M
Dear, I have same issue. Could you send me the same code if you have fixed ?
rangaak2000@gmail.com
thanks
Hi, Thank you so much for this code, it works amazing.
Is there a way to use VBA to compose an email in gmail (exactly as above) but instead of NewMail.Send, I want to save as draft in gmail?
Thanks,
Megan
How do i apply the gmail code to a button to send an email when clicked.
The email would need to contain the information on the sheet.
basically i have a form i want people to fill out and simply click the button, sending an email
with the information they just inserted.
Thanks in advance,
James
Hi James,
You can read here how to assign macro to a command button http://learnexcelmacro.com/wp/2016/04/link-macro-to-a-command-button/
Using the data from the form they are filling.. take the data form it as email body and use this send email function to send email.
Let me know if this helps.
great work
Hi guys!
I did the macro and worked everything OK. Now I need to send the e-mails through gmail to 100 different e-mails with the same attachment. One to each changing only the e-mail, the title and a name on the body. I have the names in column A and e-mails in column B. I tried to use this but is only sending to the last one with multiple of the same attachment.
Need some help.
Sir,
am having excel macro file where file are get slipt and mail will go for respective ID’s am getting this type of error please help
” incorrect Credentials!! –The message could not be sent to the SMTP server .The transport error code was 0x80040217.the server response not availbe”
I’ve been trying the code to send emails from Excel using my Gmail account, but Google blocks it and sends an alert to my reference mail. How can I avoid this to send the emails??
Hi,
Code sending email from Gmail is showing an error that your internet connection is not available.
So i checked my internet connection however there is no any problem with internet, its working properly.
Can you help me.
Hello,
I have copied your coding and adjusted to include the details needed for my specific needs. (i.e. account info; to; body; cc; subject etc). but it keeps giving me a “Object Required” Error.
Can you please help!
here is my coding…
Sub SendEmailUsingGmail()
Dim NewMail As Object
Dim mailConfig As Object
Dim fields As Variant
Set NewMail = CreateObject(“CDO.Message”)
Set mailConfig = CreateObject(“CDO.Configuration”)
‘load all default configurations
mailConfig.Load -1
Set fields = mailConfig.fields
‘Set All Email Properties
With NewMail
.To = “”
.Subject = “”
.CC = “”
.textbody = “”
End With
(and then I have all the “With fields” info)
then…
NewMail.Configuration = mailConfig
NewMail.Send
MsgBox (“Mail has been Sent”)
End Sub
Hi,
Can you please help me with the list of error number and description for bounced mails/undelivered mails
Nice post! Thank you.
Is there a limit to the number of emails sent using VBA?
we are unable to send more than 20 mails
I am facing a problem. My email is landing in the spam box of the recipient. PLEASE PROVIDE A SOLUTION!!
thank you so much.. this help me a lot..
Thank you, works but I have one problem.
After sending the email, my Excel becomes “slow” and unresponsive. For example, if I change a cell, it wont update until I have changed tabs or scrolled away. It seems like it is actually updating, but not showing it on the screen.
As I don’t get any errors, I have no clue where the conflict might be. Any suggestions?
I deleted: MsgBox (“Mail has been Sent”)
And know it works!
I don’t have outlook app but have an outlook mail ID. I need to create a macro that open the default web browser, then go to a outlook mail address in order to send multiple mails for multiple recipients. Macro should create a new mail and fill all the necessary things and send the mail.
Sir,
I need to add date with subject. to send daily reports. how can it possible. i just tried code
.Subject = “Daily Sales Report”,(Date, “dd-mmm-yyyy”)
but error delivers
Dear friends,
I have a facing a problem when it’s run the vba code ……I am not a developer just like you …. collect some codes and trying to generate a payslip through my salary sheet……so pls solve the following.. error on the line “” Set fields = mailConfig.fields..”””
Private Sub SendEmailUsingGmail_Click()
On Error GoTo Err
Dim NewMail As Object
Dim mailConfig As Object
Dim fields As Variant
Dim msConfigURL As String
Do While a <= 1
EmpID = Sheet1.Range("B14").Offset(a, 0).Value
Sheet2.Range("A8").Value = EmpID
Filename = Sheet2.Range("B9").Value & "-" & Range("B10").Value & ".pdf"
'Sheet2.PrintOut from:=1, To:=1
Sheet2.ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & "\" & Filename
Set NewMail = CreateObject("CDO.Message")
Set mailConfig = CreateObject("CDO.Configuration")
' load all default configurations
mailConfig.Load -1
Set fields = mailConfig.fields
'Set All Email Properties
With NewMail
.Subject = Sheet2.Range("A7").Value
.From = "zoom.compliance01@gmail.com"
.To = Sheet2.Range("B57").Value
.CC = ""
.BCC = ""
.TextBody = "Please find attachment"
.Attachments.Add (ThisWorkbook.Path & "\" & Filename)
End With
msConfigURL = "http://schemas.microsoft.com/cdo/configuration"
With fields
'Enable SSL Authentication
.Item(msConfigURL & "/smtpusessl") = True
'Make SMTP authentication Enabled=true (1)
.Item(msConfigURL & "/smtpauthenticate") = 1
'Set the SMTP server and port Details
'To get these details you can get on Settings Page of your Gmail Account
.Item(msConfigURL & "/smtpserver") = "smtp.gmail.com"
.Item(msConfigURL & "/smtpserverport") = 465
.Item(msConfigURL & "/sendusing") = 2
'Set your credentials of your Gmail Account
.Item(msConfigURL & "/sendusername") = "zoom.compliance01@gmail.com"
.Item(msConfigURL & "/sendpassword") = "************"
'Update the configuration fields
.Update
End With
NewMail.Configuration = mailConfig
NewMail.Send
MsgBox ("Mail has been Sent")
Exit_Err:
Set NewMail = Nothing
Set mailConfig = Nothing
End
Err:
Select Case Err.Number
Case -2147220973 'Could be because of Internet Connection
MsgBox " Could be no Internet Connection !! — " & Err.Description
Case -2147220975 'Incorrect credentials User ID or password
MsgBox "Incorrect Credentials !! — " & Err.Description
Case Else 'Rest other errors
MsgBox "Error occured while sending the email !! — " & Err.Description
End Select
Resume Exit_Err
Loop
Set NewMail = Nothing
Set mailConfig = Nothing
End Sub
When i tried running Excel vb script to send an email to outlook using SMTP, but I’m getting the Error, “Run -time error ‘-2147220973(80040213)’: The transport failed to connect to the server.As you have mentioned i have checked my port number and it seems correct, actually i need to send an email from my work account. The below are the details i’m using to send an email.
.Item(msConfigURL & “/smtpserver”) = “smtp.bcbsnc.com”
.Item(msConfigURL & “/smtpserverport”) = 25
I have aslo cross checked the details using command prompt and using the command (telnet smtp.bcbsnc.com 25) and was able to send a test message, but using excel VBA i could not send. Could you please check and provide your inputs/suggestions to solve this issue. I guess, in outlook macros were disabled and due to this i’m facing error or not. Can you check on this also.
Copy and pasted code, worked first time! Awesome, thank you very much!
I used this code to send email through GMAIL ..it worked perfectly but Arabic texts don’t show in emails on the other side…it only shows symbols not Arabic letters…it seems its an encoding problem, i guess…Can you help me with that…
Hi
I am trying to use the send email from Gmail VBA code and I am getting error “incorrect credentials” in-spite of entering correct email id and password. The transport error code was 0x80040217. Will you please help me to correct the same.
i WOULD LIKE TO SEND EXCEL DATA BY GMAIL. PLEASE HELP ME ON THIS WHERE DO I HAVE TO CHANGE
Sub Macro1()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets(“Sheet1”)
‘create a CDO object
Dim NewMail As Object
Dim mailConfig As Object
Dim OA As Object
Dim msg As Object
‘late binding
Set NewMail = CreateObject(“CDO.Message”)
Set mailConfig = CreateObject(“CDO.Configuration”)
Set OA = CreateObject(“gmail.Application”)
Dim i As Integer
Dim j As Integer
Dim last_row As Integer
last_row = Application.WorksheetFunction.CountA(sh.Range(“M:M”))
For i = 2 To last_row
Set msg = OA.createitem(0)
msg.To = sh.Range(“N” & i).Value
msg.CC = sh.Range(“O” & i).Value & ” ; ” & sh.Range(“P” & i).Value & ” ; ” & sh.Range(“Q” & i).Value
msg.Subject = “Order Dispatch Details”
msg.htmlbody = “Dear Concern, Please find below order dispatch statustable, th, td { border: 1px solid black; border-collapse: collapse;}” & _
“” & sh.Range(“A1”).Value & “” & sh.Range(“B1”).Value & “” & sh.Range(“C1”).Value & “” & sh.Range(“D1”).Value & “” & sh.Range(“E1”).Value & “” & sh.Range(“F1”).Value & “” & sh.Range(“G1”).Value & “” & sh.Range(“H1”).Value & “” & sh.Range(“I1”).Value & “” & sh.Range(“J1”).Value & “” & sh.Range(“K1”).Value & “” & sh.Range(“L1”).Value & “” & sh.Range(“M1”).Value & _
“” & sh.Range(“A” & i).Value & “” & sh.Range(“B” & i).Value & “” & sh.Range(“C” & i).Value & “” & sh.Range(“D” & i).Value & “” & sh.Range(“E” & i).Value & “” & sh.Range(“F” & i).Value & “” & sh.Range(“G” & i).Value & “” & sh.Range(“H” & i).Value & “” & sh.Range(“I” & i).Value & “” & sh.Range(“J” & i).Value & “” & sh.Range(“K” & i).Value & “” & sh.Range(“L” & i).Value & “” & sh.Range(“M” & i).Value & “”
For j = (i + 1) To last_row
If sh.Range(“N” & i).Value = sh.Range(“N” & j).Value Then
msg.htmlbody = msg.htmlbody & “” & sh.Range(“A” & j).Value & “” & sh.Range(“B” & j).Value & “” & sh.Range(“C” & j).Value & “” & sh.Range(“D” & j).Value & “” & sh.Range(“E” & j).Value & “” & sh.Range(“F” & j).Value & “” & sh.Range(“G” & j).Value & “” & sh.Range(“H” & j).Value & “” & sh.Range(“I” & j).Value & “” & sh.Range(“J” & j).Value & “” & sh.Range(“K” & j).Value & “” & sh.Range(“L” & j).Value & “” & sh.Range(“M” & j).Value & “”
i = j
End If
Next j
msConfigURL = “http://schemas.microsoft.com/cdo/configuration”
msg.htmlbody = msg.htmlbody & “Regards,Shilpa Nigam, Mobile no: +91-7290019439”
msg.Send
Next i
MsgBox “Sent”
End Sub
I made the changes in the coding but unable to send mail through same
Sub send_email_via_Gmail()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets(“Sheet1”)
Dim myMail As CDO.Message
Set myMail = New CDO.Message
Dim i As Integer
Dim j As Integer
Dim last_row As Integer
myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpusessl”) = True
myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smptauthenticate”) = 1
myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserver”) = “smtp.gmail.com”
myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = 25
myMail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendusing”) = 2
myMail.Configuration.Fields.Update
last_row = Application.WorksheetFunction.CountA(sh.Range(“X:X”))
For i = 2 To last_row
Set myMail = OA.createitem(0)
myMail.To = sh.Range(“Y” & i).Value
myMail.CC = sh.Range(“Z” & i).Value & ” ; ” & sh.Range(“AA” & i).Value & ” ; ” & sh.Range(“AB” & i).Value
myMail.Subject = “Order Dispatch Details”
myMail.htmlbody = “Dear Concern, Please find below dispatch statustable, th, td { border: 1px solid black; border-collapse: collapse;}” & _
“” & sh.Range(“A1”).Value & “” & sh.Range(“B1”).Value & “” & sh.Range(“C1”).Value & “” & sh.Range(“D1”).Value & “” & sh.Range(“E1”).Value & “” & sh.Range(“F1”).Value & “” & sh.Range(“G1”).Value & “” & sh.Range(“H1”).Value & “” & sh.Range(“I1”).Value & “” & sh.Range(“J1”).Value & “” & sh.Range(“K1”).Value & “” & sh.Range(“L1”).Value & “” & sh.Range(“M1”).Value & “” & sh.Range(“N1”).Value & “” & sh.Range(“O1”).Value & “” & sh.Range(“P1”).Value & “” & sh.Range(“Q1”).Value & “” & sh.Range(“R1”).Value & “” & sh.Range(“S1”).Value & “” & sh.Range(“T1”).Value & “” & sh.Range(“U1”).Value & “” & sh.Range(“V1”).Value & _
“” & sh.Range(“W1”).Value & “” & sh.Range(“X1”).Value & _
“” & sh.Range(“A” & i).Value & “” & sh.Range(“B” & i).Value & “” & sh.Range(“C” & i).Value & “” & sh.Range(“D” & i).Value & “” & sh.Range(“E” & i).Value & “” & sh.Range(“F” & i).Value & “” & sh.Range(“G” & i).Value & “” & sh.Range(“H” & i).Value & “” & sh.Range(“I” & i).Value & “” & sh.Range(“J” & i).Value & “” & sh.Range(“K” & i).Value & “” & sh.Range(“L” & i).Value & “” & sh.Range(“M” & i).Value & “” & sh.Range(“N” & i).Value & “” & sh.Range(“O” & i).Value & “” & sh.Range(“P” & i).Value & “” & sh.Range(“Q” & i).Value & “” & sh.Range(“R” & i).Value & “” & sh.Range(“S” & i).Value & “” & sh.Range(“T” & i).Value & “” & sh.Range(“U” & i).Value & “” & sh.Range(“V” & i).Value & “” & sh.Range(“W” & i).Value & “” & sh.Range(“X” & i).Value & “”
For j = (i + 1) To last_row
If sh.Range(“Y” & i).Value = sh.Range(“Y” & j).Value Then
myMail.htmlbody = myMail.htmlbody & “” & sh.Range(“A” & j).Value & “” & sh.Range(“B” & j).Value & “” & sh.Range(“C” & j).Value & “” & sh.Range(“D” & j).Value & “” & sh.Range(“E” & j).Value & “” & sh.Range(“F” & j).Value & “” & sh.Range(“G” & j).Value & “” & sh.Range(“H” & j).Value & “” & sh.Range(“I” & j).Value & “” & sh.Range(“J” & j).Value & “” & sh.Range(“K” & j).Value & “” & sh.Range(“L” & j).Value & “” & sh.Range(“M” & j).Value & “” & sh.Range(“N” & j).Value & “” & sh.Range(“O” & j).Value & “” & sh.Range(“P” & j).Value & “” & sh.Range(“Q” & j).Value & “” & sh.Range(“R” & j).Value & “” & sh.Range(“S” & j).Value & “” & sh.Range(“T” & j).Value & “” & sh.Range(“U” & j).Value & “” & sh.Range(“V” & j).Value & “” & sh.Range(“W” & j).Value & “” & sh.Range(“X” & j).Value & “”
i = j
End If
Next j
myMail.htmlbody = myMail.htmlbody & “Regards,Vikas Karn, Mobile no: +91-83 7795 2790”
myMail.Send
Next i
MsgBox “Sent”
End Sub
How to send Email from Gmail in Excel Macro with multiple attachment from liste
Hi You can simply add a for loop to add as many attachments as you want from a list
With NewMail .Subject = "Test Mail from LearnExcelMacro.com" .From = "vishwamitra01@yahoo.com" .To = "vishwamitra02@gmail.com;info@learnexcelmacro.com" .CC = "vishwamitra01@gmail.com" .BCC = "" .HTMLBody = "Write your complete HTML Page" Dim attachments as Variant attachments = ("C:\Macro-help-1.xls", "C:\Macro-help-2.xls", "C:\Macro-help-3.xls") for each attachment in attachments .AddAttachment attachment next End With