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/ (http://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/ (http://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 (http://www.rondebruin.nl/pdf.htm)

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 (http://www.rondebruin.nl) 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 :)