Ever got a need to send email from excel VBA to multiple email ids, provided in an excel sheet? Want to learn how to send mails from Excel without opening outlook? This article covers, how you can automate outlook and send multiple emails with attachments from Excel in one click, keeping people in Cc and Bcc too.
In the end, you can download the file too, for sending males instantly to hundreds of ids. The file contains macro to send email from excel.
How to send mail from Excel?
To send mail from Excel, you need to understand these VBA snippets.
Important: You must have an email configured on Outlook Application.
Press F11 to open VB Editor.
Dim outApp As Outlook.Application 'This will create a reference to outlook object. Dim outApp As Outlook.MailItem 'This will create a reference to MailItem.
Set outApp = New Outlook.Application Set outMail = outApp.CreateItem(0)
With outMail .To = "abcd.mail.com" 'Mandatory. Here you define the destination mail id. .cc = "cc.mail.com" 'optional. Cc mail id if you want. .BCC = "bcc.mail.com" 'optional. Bcc mail id if you want. .Subject = subj 'should have. The massage on mail body. .Body = msg 'optional. The massage on mail body. .Attachments.Add "C:/exceltip.com\test.xlsx" 'optional. Fully qualified name of attachment. .Send 'Mandatory if you want to send mail from excel. You you want to see the mail in outlook, use .Display method. End With
That's it. Its all you need to send mail from excel using VBA.
Now let's see an example that sends mail to each email id in an Excel column separately.
Imagine, you are provided with an excel sheet that contains 100 email ids, and you need to send an email to each email id separately. Not only this, you need to attach items too. This would take you hours if you do it manually. So let's automate this mailing task in Excel VBA.
Here I have this data. In column C, titled "Send Mail To", there are several email ids. I need to send an email to each email id in this column.
For this, I have created columns for Subject, Massage body, CC and BCC.
The "Send Mail To" should not have any blank cell in between. All other columns can have blank cells. If you want
multiple email ids in to then write those email ids with commas in this column. Make sure email ids are valid.
If you want to send attachments. Write addresses of those files in the attachment column.
Write the subject in the Subject column.
Massage in Massage Body column.
If you want to keep someone in cc, write his/her email id in the CC column. Same for BCC. use comma for multiple email ids.
Now to send emails to each email id in column follow these steps.
'*** You must have a Outlook email configured in outlook application on your system *** '*** add reference to outook object library from references in tools *** Sub BulkMail() Application.ScreenUpdating = False ThisWorkbook.Activate 'Creating references to Application and MailItem Objects of Outlook Dim outApp As Outlook.Application Dim outMail As Outlook.MailItem 'Creating variable to hold values of different items of mail Dim sendTo, subj, atchmnt, msg, ccTo, bccTo As String Dim lstRow As Long 'My data is on sheet "Exceltip.com" you can have any sheet name. ThisWorkbook.Sheets("Exceltip.com").Activate 'Getting last row of containing email id in column 3. lstRow = Cells(Rows.Count, 3).End(xlUp).Row 'Variable to hold all email ids Dim rng As Range Set rng = Range("C2:C" & lstRow) 'initializing outlook object to access its features Set outApp = New Outlook.Application On Error GoTo cleanup 'to handle any error during creation of object. 'Loop to iterate through each row, hold data in of email in variables and send 'mail to each email id. For Each cell In rng sendTo = Range(cell.Address).Offset(0, 0).Value2 subj = Range(cell.Address).Offset(0, 1).Value2 & "-MS" msg = Range(cell.Address).Offset(0, 2).Value2 atchmnt = Range(cell.Address).Offset(0, -1).Value2 ccTo = Range(cell.Address).Offset(0, 3).Value2 bccTo = Range(cell.Address).Offset(0, 4).Value2 On Error Resume Next 'to hand any error during creation of below object Set outMail = outApp.CreateItem(0) 'Writing and sending mail in new mail With outMail .To = sendTo .cc = ccTo .BCC = bccTo .Body = msg .Subject = subj .Attachments.Add atchmnt .Send 'this send mail without any notification. If you want see mail 'before send, use .Display method. End With On Error GoTo 0 'To clean any error captured earlier Set outMail = Nothing 'nullifying outmail object for next mail Next cell 'loop ends cleanup: 'freeing all objects created Set outApp = Nothing Application.ScreenUpdating = True Application.ScreenUpdating = True End Sub
The above code will send emails to each email id in a row of “send mail to” column. Now to run this code, you can directly run it from here. Or you can add a figure or button in your workbook and then assign this macro to that object. I use shapes because of they customizable. To do so, follow the below steps.
Now whenever you’ll click on this object, your macro will run and send emails. Check sent mails to ensure.
You can download Excel Mail Sender File here.
Split Excel Sheet Into Multiple Files Based On Column Using VBA | To split a worksheet into multiple files, based on a column's values, you'll need to filter it copy-paste it into a new file.
How to Filter the Data in Excel using VBA | Filtering data using VBA is easy. These simple lines of codes filter the data on the given criteria.
Turn Off Warning Messages Using VBA In Microsoft Excel 2016 | While performing multiple tasks, like opening and closing files, excel shows warning messages so that you don't lose data. They interrupt the running code. To avoid them
How To Loop Through Sheets In Excel Using VBA | To back and forth on two or more sheets, we need to loop through them. To loop through multiple sheets, we use...
7 Examples of For Loops in Microsoft Excel VBA | The for loop is the most used looping technique in any language. Excel VBA is no exception.
50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
How to use the VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
How to use the COUNTIF function in Excel | Count values with conditions using this amazing function. You don't need filter your data to count specific value. Countif function is essential to prepare your dashboard.
How to use the SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.