email PDF via Outlook with vb code - PDF is created and saved from and Excel file

[tcrjmom]

Hello All,

Must start off by stating that I've tried another website to get an answer but have been unable to get one so I'm trying here. The link to my question on the other website is - Help to combine two Modules from seperate workbooks to one workbook preform task

OK - so I've been able to find code to do the following:

Have a user click on a button in the Excel Spreadsheet which then triggers the following:
Brings a pop up screen that needs to be checked before going forward (UserForm1)
After box it checked the a PDF document is created and saved with a specific name in folder that specified in the code.
The PDF opens
The UserForm1 dialog box is close
and finally the Active Workbook stays open so that the next invoice and be completed.

All of the above works great - it's the issues below that I need help with:

1.) I would like code to take the PDF document that has been saved and is now open on the screen to be placed in an email
2.) I would like the "TO" address to be populated from a cell that was in the orginal file cell "F13"
3.) I would like the "FROM" address to be a hard coded email address
3.) I would like the Subject - have a statement & name of the Saved PDF Document
4.) and lastly I would like to be able to place a comment in the body of the email

I've been searching High and Low for this code and have found some, but I think the problem is that I don't know how to translate the name of Saved PDF into the code for Outlook so Outlook doesn't know what file to upload and Send.

I'm not even sure if what I'm asking can be done, but I WOULD GREATLY appreciate any help you can offer. Below is the current code that I'm using:

UserForm1 (click button on Excel Spreadsheet)

Private Sub Checkbox1_Click() 'If not Checkbox 1, change to appropriate index number in title and elsewhere in the code.
If CheckBox1.Value = True Then
 Customer_Prep_Email
 CheckBox1.Value = False
 UserForm1.Hide
 End If
End Sub

Private Sub UserForm_Click()
End Sub

Module1 - this creates the Saved PDF

 'user can create email only if check box is selected
   
'This macro will allow the document to be prepared and send to us
Sub Customer_ORDER_Acknowledgement()
    
    UserForm1.Show
    
End Sub
Sub Customer_Prep_Email()
    Sheets("Order").Copy
filenam = "C:\Users\ORDER " & Range("F4") & (" ") & Range("C5") & (" ") & Range("F5") & (" ") & Format(Now(), "mmddyy") & (" ") & "Order Statement Acknowledged" & ".xlms"
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\ORDER " & Range("F4") & (" ") & Range("C5") & (" ") & Range("F5") & (" ") & Format(Now(), "mmddyy") & (" ") & "Order Statement Acknowledged" & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
        
   
   ActiveWorkbook.Close (False)
End Sub
Sub AttachActiveSheetPDF_01()
Set Mail_Object = CreateObject("Outlook.Application")
        With Mail_Object.CreateItem(o)
            .Subject = "Monthyl Billing"
            .To = Range("F13")
            .Body = "YOUR COMMENT HERE"
            .Attachments.Add "YOUR FILE NAME HERE"
            .Send
    End With
 
Windows("Order Form.xlsm").Activate

End Sub

 

[Andrew Poulsom]

Maybe this will help you:

Create and Mail PDF files with Excel 2007/2013

 

[tcrjmom]

Thank you Andrew. From what I can tell, I need to get the Microsoft Office Service Pack 2 installed before I can use write the code? It appears that there is a Macro that is included in the Service Pack, is that correct? The only problem is, I'm going to be sending this file to our customers and I will need to have them install the Service pack before they use it - is that correct?

 

[Andrew Poulsom]

That applies only to Excel 2007.

 

[tcrjmom]

THANK YOU - THANK YOU - THANK YOU! Andrew, I had seen the file that you sent me; however, I didn't understand it, so I was finally able to work through the files and got - everything works PERFECT! Thank you so much for your assistance, it's GREATLY appreciated!