send pre-defined email to email list from excel

excel01noob

Board Regular
Joined
Aug 5, 2019
Messages
93
Office Version
  1. 365
  2. 2016
Dear all

I have currently a macro which I run to generate a report.
Once finished the report needs to be sent to several people.

The email list of people, subject of the email and body text are saved under the tab "email" as you can see below

8DB18sKa5mnOsAAAAASUVORK5CYII=




I want to also only send the tab named "payment list" from this active workbook.



The date will have to be today's date which I have combined in the email subject and body like this:
="Payments report VD "& TEXT(TODAY(),"dd/mm/yyyy")

Is this feasible ?

Thank you in advance.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Try this

Change data in red by your information


Code:
Sub Mail_Sheet()
  Dim wPath As String, wFile As String, dam As Object
  Dim wMail As String, wSubj As String, wBody As String
  
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  wFile = "Payments report VD " & Format(Date, "[COLOR=#0000ff]dd-mm-yyyy[/COLOR]")  '[COLOR=#0000ff]must be hyphen[/COLOR]
  wPath = ThisWorkbook.Path & "\"
  
  wMail = Sheets("email").Range("[B][COLOR=#ff0000]A2[/COLOR][/B]").Value   '[COLOR=#008000]people separated by semicolons[/COLOR]
  wSubj = Sheets("email").Range("[B][COLOR=#ff0000]B2[/COLOR][/B]").Value   '[COLOR=#008000]subject[/COLOR]
  wBody = Sheets("email").Range("[B][COLOR=#ff0000]C2[/COLOR][/B]").Value   '[COLOR=#008000]body[/COLOR]
  
  Sheets("payment list").Copy
  ActiveWorkbook.SaveAs Filename:=wPath & wFile & ".xlsx"
  ActiveWorkbook.Close False
  
  Set dam = CreateObject("Outlook.Application").CreateItem(0)
  dam.to = wMail
  dam.Subject = wFile & " " & wSubj
  dam.Body = wFile & vbCr & wBody
  dam.Attachments.Add wPath & wFile & ".xlsx"
  dam.Display   '   'or use .Send
  
  Set dam = Nothing
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top