How To Send Emails Automatically From MS Excel

0
157
How to send email automatically in excel

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

how to sent emails automatically from excel

The Process

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.


Also Read: How to speed up excel file loading and execution


Activate the developer menu:

To activate the developer menu Right click on Ribbon bar-> Customize the Ribbon -> Select Developer under the main tab.

Create Macro

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

Or

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.

Wrapping Up!

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.

RiansClub is also available on FacebookTwitter and Instagram. If you wish you can connect through those social platforms.

If you hate reading you can visit our YouTube channel to watch videos on many topics posted in this blog. You can also download RiansClub Android app so that you can be connected all the time.

LEAVE A REPLY

Please enter your comment!
Please enter your name here