This article is for those who are looking to automate excel reports. Almost every business have reports being sent periodically. May be weekly, daily, or hourly. Here i incorporate the vba script to send email automatically using macro in excel. You can directly use it in your vba code replacing your credentials.
Here i am using gmail to send email from the macro.
Sub SendEmailUsingGmail() Dim sMsgBody As String 'sMsgBody is a string variable to add body to email sMsgBody = "Hi All," & vbNewLine sMsgBody = sMsgBody & "Please find the attached Report" & vbNewLine sMsgBody = sMsgBody & "Thank You," & vbNewLine sMsgBody = sMsgBody & "Myself" & vbNewLine 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") = "youremail" NewMail.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "emailpassword" 'Update the configuration fields NewMail.Configuration.Fields.Update 'Set All Email Properties With NewMail .Subject = "Monthly Report" .From = "[email protected]" .To = "[email protected];[email protected]" ' you can add multiple attachments and multiple recipients .AddAttachment "pathoftheattachmentfile" .TextBody = sMsgBody NewMail.Send End With End Sub
The above code is self explanatory, also i have added comments to the code to make you understand easily. You can play with the code to attach multiple files, include multiple recipients.
If you are using
Go to Less Secure Apps
Turn on the feature to allow excel to send email using SMTP Server. You can use any email your company’s email to send the email with
Please Share if you find it useful. Please comment below if you face any issues. I will respond asap.
The post How to send email automatically from excel using macro? appeared first on TechDrips.