vba to send sheet as pdf in outlook with subject that is in a1

[lpratt]

simple i know but i cannot work this out.

I want to button that will attach current sheet named template as a pdf to a outlook email
There are no set recipients this will be entered in Outlook
Subject line will be what is in cell a1

that is it

 

[DanteAmor]

Try this:

Sub MailActiveSheet()
  Dim wPathFile As String
  
  wPathFile = ThisWorkbook.Path & "\template.pdf"
  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=wPathFile, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

  With CreateObject("Outlook.Application").CreateItem(0)
    .To = ""
    .Subject = ActiveSheet.Range("A1").Value
    .Body = ""
    .Attachments.Add wPathFile
    .Display
  End With
End Sub

Regards
Dante Amor

 

[Remal]

Hi Dante

How do you change the file name? And is is posible to add a signature to the mail?

Brgds
René

 

[Trevor G]

If you have a signature block setup in Outlook it will be added to the new email or if you have several you can select the correct one once the email is displayed. As far as the file name does this come from a cell in Excel or do you want to add a name via another method like an input box?