send pre-defined email to email list from excel

[excel01noob]

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

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.

 

[daverunt]

This should help. Mail one sheet.

https://www.rondebruin.nl/win/s1/outlook/mail.htm

 

[DanteAmor]

Try this

Change data in red by your information

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