Microsoft Excel is the most popular productivity tools widely used by many corporates. In big corporates, it’s a usual practice to send work reports or activity sheet very often to reporting managers. In some corporates, associates have to send their daily activity report. Those activity reports are usually made in excel and they need to send that excel file on a daily basis. Although it’s not a tidy job but some people prefer some automation so that that excel file automatically gets emailed daily. So in this article, we will discuss How To Send Emails Automatically In MS Excel
Microsoft Excel does not provide any default command for sending automated emails. To achieve that you will have to use a macro. Let’s see how to create and execute that macro.
To create a macro you will have to follow the below process.
Activate the developer menu:
To activate the developer menu Right click on Ribbon bar-> Customize the Ribbon -> Select Developer under the main tab.
To create the macro you will have to follow the below process. Go to Developer Menu -> Macros -> Enter Macro Name -> Click on Create
Codes for the Macro
Once you click on macro it will open the typical visual basic editor. Delete everything and paste below codes in that editor.
Sub Mail_Workbook_1() ActiveWorkbook.Save Dim OutApp As Object Dim OutMail As Object Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail .To = "[email protected]" .CC = "" .BCC = "" .Subject = "Activity Sheet Notification" .HTMLBody = "<body><p>This is to notify that " & Range("f6").Value & " has updated the activity sheet. You can review the activity sheet by clicking on the link here. " & vbNewLine & _ "<a href='https://google.com'>" & vbNewLine & _ "Open Activity Sheet</a>. Thank You!" .Send End With On Error GoTo 0 Set OutMail = Nothing Set OutApp = Nothing End Sub Sub Lastfilledcell() Range("b1000").End(xlUp).Select End Sub
Run The macro
To run the macro you can go to…
Developer Menu -> Macros -> Select the macro-> Run
You can create a tab and insert the code into that. You will have to just click on that tab to send emails automatically.
To insert a tab you can Go to Developer Menu -> Insert ->Button- -? Assign the macro that you have just created
Click on that button every time when you want to send emails. No need to go to outlooks and send emails.
This macro is useful if you want to automate the process. Although you can add many codes to enhance the functionalities of this macros.
That?s all we have in this article. Hope you liked the article and found some useful information. If you have any queries please do write in the comment section and I will be happy to answer. If you find this article helpful please rate us five stars.