Forum Discussion

Copper Contributor

May 30, 2017

Solved

Re: Macro to save as PDF with auto filename as cell value

Hi I recently used the following Macro, provided by Gary's Student, to create a PDF from an active sheet, generate a unique Filename based on a cell ref and save it to a specific location. This m...

Macros & VBA

Need Help

Andrew Jones

Jun 01, 2017

This is code I use.

End Sub

Private Sub Email()

    Dim objOutlook As Object
    Dim objMail As Object
    Dim signature As String
    Dim oWB As Workbook
    Set oWB = ActiveWorkbook
    
    Set objOutlook = CreateObject("Outlook.Application")
    Set objMail = objOutlook.CreateItem(0)
    
        
    With objMail
        .Display
    End With
        signature = objMail.HTMLbody
    With objMail
        .To = oWB.Sheets("Sheet1").Range("A1").Value
        ''.SentOnBehalfOfName = ""
        .Subject = strMySubject
        ''.body = "Dear Sir," & vbNewLine & vbNewLine & "Add stuff here" & vbNewLine & vbNewLine & signature
        .HTMLbody = "<font face=" & Chr(34) & "Calibri" & Chr(34) & " size=" & Chr(34) & 4 & Chr(34) & ">" & "Dear Sir," & "<br> <br>" & "Add stuff here" & "<br> <br>" & signature & "</font>"
        .Attachments.Add (strSaveFileName + ".pdf")
        .Display
    End With

    Set objOutlook = Nothing
    Set objMail = Nothing
    
End Sub

Joe Gray

Copper Contributor

Jun 02, 2017

Andrew

Just to let you know that the code below works well for me, and thank you again.

Command Button: -

Generates a pdf of the ActiveSheet
Saves pdf to a specific location, indicated in Cell H1, with an auto Filename based on the Sheet Date within Cell I3, formatted as YYMMDD
Opens Outlook and displays email with pdf attached (file with date "Now")
Auto fills Recipients, reading from Cell C50
Email Body as required

When i'm happy i will change code to automatically send email not display.

for reference:-

Cell H1: ="I:\2017 - 2018\Operations Unit\Day Sheets\"&"DS_"&TEXT(I3,"yymmdd")&".PDF"

Private Sub Email_Sheet_Click()

Dim objOutlook As Object
    Dim objMail As Object
    Dim signature As String
    Dim oWB As Workbook
    Set oWB = ActiveWorkbook
    
    
    
     s = Range("h1").Value	
  
 '
     ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
         s, Quality:=xlQualityStandard, IncludeDocProperties _
         :=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
         
         
    PDF_File = "Insert Path here\DS_" & Format(Now, "YYMMDD") & ".pdf"
 
    
    Set objOutlook = CreateObject("Outlook.Application")
    Set objMail = objOutlook.CreateItem(0)
        
    With objMail
        .Display
    End With
        signature = objMail.HTMLbody
    With objMail
        .To = ActiveSheet.Range("C50")
        .Cc = ActiveSheet.Range("C55")
        .Subject = "Insert Subject Here"
        .HTMLbody = "<font face=" & Chr(34) & "Calibri" & Chr(34) & " size=" & Chr(34) & 4 & Chr(34) & ">" & "Hi," & "<br> <br>" & "Insert email body here" & "<br> <br>" & signature & "</font>"
        .Attachments.Add PDF_File
        .Save
        .Display
    End With

    Set objOutlook = Nothing
    Set objMail = Nothing
End Sub

Gerald Edwards

Copper Contributor

May 14, 2018

I'm very new to coding, so I wanted to add a little twist to this thread. Is it possible to have a code to convert the current spreadsheet to PDF, create an email through Outlook, but not save the document?

Thanks!

Matt Mickle

Bronze Contributor

May 14, 2018

From what I remember it's necessary to save the document to attach the file. However, you can save the PDF to a temporary directory and then once done using it you can just delete it:

It would look something like this:

     TempFilePath = Environ$("temp") & "\" 'This defines the filepath ---> C:\Users\username\AppData\Local\Temp
     TempFileName = "Your File Name Goes Here" 'Name File      
     ActiveWorkbook.SaveAs TempFilePath & TempFileName 'Save file in Temporary Directory
        
    'Email Workbook to people
    With OutMail
        .To = "YourName@Email.Com"
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = "This is the email body"  'Use "Blah Blah Blah" & Chr(13) & "This is another line"
        .Attachments.Add TempFilePath & TempFileName
        .Send
    End With
    
    'Delete the Temporary File 
    Kill TempFilePath & TempFileName