Sending E-mails w/PDF Attachments via Command Buttons in Excel

[zyousafi]

I want to create a command button that will "print" the current worksheet (i.e. an evaluation form) an evaluator is working on as a PDF and attach that PDF (i.e. the evaluation form) to an e-mail that will be sent to the person he/she is evaluating. The command button should also pick the name of the person he/she is evaluating from a "MASTER" sheet that contains Windows NT User IDs, names, and e-mail addresses when the evaluator puts a Windows NT User ID in the evaluation form.

[Excel Fox]

Sounds more of a project request than a help request. Suggest you take some inputs from the following links, and let us know if you need any help
www.excelfox.com/forum/f2/send-multiple-attachments-through-outlook-using-excel-template-400/
www.excelfox.com/forum/f18/send-outlook-mail-signature-range-excel-multiple-email-ids-304/

[joopw]

I want to create a command button that will "print" the current worksheet (i.e. an evaluation form) an evaluator is working on as a PDF and attach that PDF (i.e. the evaluation form) to an e-mail that will be sent to the person he/she is evaluating. The command button should also pick the name of the person he/she is evaluating from a "MASTER" sheet that contains Windows NT User IDs, names, and e-mail addresses when the evaluator puts a Windows NT User ID in the evaluation form.

Take a look at Ron de Bruin's website.
Create and Mail PDF files with Excel 2007/2010

Enjoy,
Joop

[zyousafi]

@joopw, thank you very much for directing me to Ron de Bruin's website. I have been able to customize the code in his original file and make that work; however when I try to replicate the exact same code in one (1) of my files I am getting the following error:

"Outlook does not recognize one or more names."

The code I am using is as follows:

'Derived from Ron de Bruin's original code. Please visit Ron de Bruin's website at Ron de Bruin Excel Automation for the original source code.

Sub SendFeedbackForm()
    
    Dim FileName As String
    Dim FixedFilePathName As String
    
    Dim OutApp As Object
    Dim OutMail As Object
    
    Dim sTo As String
    Dim sCC As String
    Dim sBCC As String
    Dim sSubject As String
    Dim sBody As String
    
    FixedFilePathName = "C:\Documents and Settings\" & fOSUserName() & "\Desktop\" & WorksheetFunction.VLookup(fOSUserName(), Worksheets("sheet1").Range("a1:c1"), 2, False) & ".pdf"

    If ActiveWindow.SelectedSheets.Count > 1 Then
        MsgBox "There is more then one sheet selected," & vbNewLine & _
               "be aware that every selected sheet will be published"
    End If

    'Ron de Bruin's original code modified.
    FileName = CreateFeedbackFormPDF(ActiveSheet, FixedFilePathName, True, False)

    If FileName <> "" Then
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
 
    sTo = "zyousafi@test.com"
    sCC = "zyousafi@test.com" & "zyousafi@test.com" & ";"
    sBCC = ""
    sSubject = "Test"
    sBody = "Test" & vbNewLine & vbNewLine & _
    "Regards," & vbNewLine & vbNewLine & "Zohair Yousafi"
        
    With OutMail
        .To = sTo
        .CC = sCC
        .BCC = sBCC
        .Subject = sSubject
        .body = sBody
        .Attachments.Add FixedFilePathName
        .send
    End With
    Set OutMail = Nothing
    Set OutApp = Nothing
    
    Else
        MsgBox "An error has occurred.  Please contact your system administrator."
    End If

    'Delete the file from the user's desktop.
    Kill FixedFilePathName
    
End Sub

Please help

[Admin]

Hi,

You miss a semi colon ( ; ) between the addresses.

replace

sCC = "zyousafi@test.com" & "zyousafi@test.com" & ";"

with

sCC = "zyousafi@test.com" & ";" & "zyousafi@test.com" & ";"

[zyousafi]

okay great! that works :D but now i am having trouble opening the saved file. excel is continuously giving the following error, "excel found unreadable content" - what does this error mean?

[Admin]

okay great! that works

Thanks for the feedback.

Since your original question has been answered, start new thread for other issues which is not relating to the original question.

This time I opened a new thread for you.

[zyousafi]

thank you