Excel VBA Save PDF within workbook folder and send as email attachment

[maxbrggs]

Hello,

I have been trying to Frankenstein some script for this to work for an embarrassing amount of time.

Having read several other threads I think what I want is much more basic than has been previously asked for...

I have managed to create a macro that saves the working sheet as a PDF and titles its self correctly then updates some a log book on a separate sheet of the inputted data.

In between these two steps I want the take the freshly minted PDF and send it as an attachment to a recipient who's email address will be a cell value and CC. in our account teams that will be a fixed email address.

Other previous threads have numerous dialog boxes popping up all over the place, or give the chance to change file name and determine where to save the file, I do not need this (the people using it will save in the wrong folders or change the file name so..). I just simply for it save the pdf on the current folder of the whole workbook then open a draft email with the attachment, populate addresses, subject name of company name and order number (again information from the worksheet) and for it to have a pre written message in the body which shouldn't need amending.

What I have so far is below....

Sub Create_PTC_pdf()

Dim pdfFileName As String


pdfFileName = Sheets("Purchase Order Template").Range("L19").Value & Range("L22").Value & Range("L25").Value & Format(Date, " - dd mm yyyy")

With Sheets("Purchase Order Template")
.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=pdfFileName, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True




Sheets("Log Sheet").Range("11:11").Insert
Sheets("Log Sheet").Range("8:8").Copy
Sheets("Log Sheet").Range("11:11").PasteSpecial xlPasteValues

Sheets("Purchase Order Template").Range("j10") = "[1-3 Word Description]"

End With


Sheets("Purchase Order Template").Range("g5").Value = Range("g5").Value + 1


End Sub


Any help would be greatly appreciated.

Kind Regards,

Max

 

[rpaulson]

I use this all the time, It uses outlook to send the message.

Sub EMail_Pdf()

Dim recipient As String
Dim EApp As Object
Set EApp = CreateObject("Outlook.Application")

Dim EItem As Object
myPath = "C:\temp\" 'your path here
myFile = "File Name"

ExportAsFixedFormat Type:=xlTypePDF, Filename:=myPath & myFile


    Set EItem = EApp.CreateItem(0)
           
    recipient = "bill@micrsoft.com"
           
    With EItem
   
        .To = recipient
   
        .Subject = "Letter from me"
   
        .Body = "Here is you letter." & vbCr & _

        vbCr & "Bob's Plumbing" & _
        vbCr & "123 Main.  St." & _
        vbCr & "New York, NY 12345" & _
        vbCr & "(555) 123-4567"


   
        .Attachments.Add (myPath & myFile & ".pdf")
   
        .Display 'this option will open outlook and wait for you to hit the send button
       ' .send 'will automatically send the email without opening outlook
       
    End With
Exit Sub



End Sub