Save as PDF and email in outlook

[antmoss3]

Hi
I am looking for a VBA code that will save an excel file in a specified folder as a PDF based on cell values and then open an email with pre defined email addresses and subject/body based on text/cell values. Does such code exist?
Thanks
Anthony

[davesexcel]

Attaching a sample workbook enables others to work on your problem:

To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and click "manage attachments" to open the upload window.

-


See clip
To add a file to a post

[antmoss3]

Thanks - see attached example.
Essentially I would like the file to be named and saved as: Collection/Return Request - (Cell C12 value) - (Cell C7 value)

Then click the send request to email button will attach the PDF to the email...

And then then the subject of the email to be the same as the file name.

The body of the email to say:

Please find attached return request form.

Regards
(Cell C8) value)

Thanks
Anthony

[antmoss3]

Thanks - see attached example.
Essentially I would like the file to be named and saved as: Collection/Return Request - (Cell C12 value) - (Cell C7 value)

Then click the send request to email button will attach the PDF to the email...

And then then the subject of the email to be the same as the file name.

The body of the email to say:

Please find attached return request form.

Regards
(Cell C8) value)

Thanks
Anthony

[bakerman2]

For starters.

Sub tst()
    With Sheet1
        fName = "Collection_Return Request - " & .Range("C12") & " - " & Replace(.Range("C7"), "/", "_")
        .ExportAsFixedFormat 0, ThisWorkbook.Path & Application.PathSeparator & fName
    End With
    With CreateObject("outlook.application").createitem(0)
        .to = "emailaddress here"
        .Subject = fName
        .body = "Please find the attached return request form." & vbLf & vbLf & _
                "Regards." & vbLf & vbLf & _
                Sheet1.Range("C8")
        .attachments.Add ThisWorkbook.Path & Application.PathSeparator & fName & ".pdf"
        .display
    End With
    'remove following line if you want to keep pdf-file
    Kill ThisWorkbook.Path & Application.PathSeparator & fName & ".pdf"
End Sub

[antmoss3]

That is spot on, the only other thing I would need is to save the PDF in a specific folder on a shared drive. Could this be added?
Thanks again

[bakerman2]

.ExportAsFixedFormat 0, ThisWorkbook.Path & Application.PathSeparator & fName

Try replacing the red part with the shared drive path.

[antmoss3]

Thanks again, whereabouts in the string should this go?

[bakerman2]

As I said replace the red part.

.ExportAsFixedFormat 0, "shared_folder_path_here" & Application.PathSeparator & fName

[antmoss3]

I meant where in the bit below it should go...?

Sub tst()
    With Sheet1
        fName = "Collection_Return Request - " & .Range("C12") & " - " & Replace(.Range("C7"), "/", "_")
        .ExportAsFixedFormat 0, ThisWorkbook.Path & Application.PathSeparator & fName
    End With
    With CreateObject("outlook.application").createitem(0)
        .to = "emailaddress here"
        .Subject = fName
        .body = "Please find the attached return request form." & vbLf & vbLf & _
                "Regards." & vbLf & vbLf & _
                Sheet1.Range("C8")
        .attachments.Add ThisWorkbook.Path & Application.PathSeparator & fName & ".pdf"
        .display
    End With
    'remove following line if you want to keep pdf-file
    Kill ThisWorkbook.Path & Application.PathSeparator & fName & ".pdf"
End Sub

[banaanas]

Sub tst()
    With Sheet1
        fName = "Collection_Return Request - " & .Range("C12") & " - " & Replace(.Range("C7"), "/", "_")
        .ExportAsFixedFormat 0, "CHANGE THIS TO THE PATH" & Application.PathSeparator & fName
    End With
    With CreateObject("outlook.application").createitem(0)
        .to = "emailaddress here"
        .Subject = fName
        .body = "Please find the attached return request form." & vbLf & vbLf & _
                "Regards." & vbLf & vbLf & _
                Sheet1.Range("C8")
        .attachments.Add "CHANGE THIS TO THE PATH" & Application.PathSeparator & fName & ".pdf"
        .display
    End With
    'remove following line if you want to keep pdf-file
    Kill "CHANGE THIS TO THE PATH" & Application.PathSeparator & fName & ".pdf"
End Sub

[bakerman2]

And also.

Sub tst()
    With Sheet1
        fName = "Collection_Return Request - " & .Range("C12") & " - " & Replace(.Range("C7"), "/", "_")
        .ExportAsFixedFormat 0, "CHANGE THIS TO THE PATH" & Application.PathSeparator & fName
    End With
    With CreateObject("outlook.application").createitem(0)
        .to = "emailaddress here"
        .Subject = fName
        .body = "Please find the attached return request form." & vbLf & vbLf & _
                "Regards." & vbLf & vbLf & _
                Sheet1.Range("C8")
        .attachments.Add ThisWorkbook.Path & Application.PathSeparator & fName & ".pdf"
        .display
    End With
    'remove following line if you want to keep pdf-file
    Kill ThisWorkbook.Path & Application.PathSeparator & fName & ".pdf"
End Sub

Or in 1 go.

Sub tst()
    mypath = "CHANGE THIS TO THE PATH"
    With Sheet1
        fName = "Collection_Return Request - " & .Range("C12") & " - " & Replace(.Range("C7"), "/", "_")
        .ExportAsFixedFormat 0, mypath & Application.PathSeparator & fName
    End With
    With CreateObject("outlook.application").createitem(0)
        .to = "emailaddress here"
        .Subject = fName
        .body = "Please find the attached return request form." & vbLf & vbLf & _
                "Regards." & vbLf & vbLf & _
                Sheet1.Range("C8")
        .attachments.Add mypath & Application.PathSeparator & fName & ".pdf"
        .display
    End With
    'remove following line if you want to keep pdf-file
    Kill mypath & Application.PathSeparator & fName & ".pdf"
End Sub

[antmoss3]

Perfect thanks for all your help on this!!!

[bakerman2]

Glad to help and thanks for rep+.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.