Results 1 to 8 of 8

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

  1. #1
    Junior Member
    Join Date
    Jul 2012
    Posts
    6
    Rep Power
    0

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

    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.

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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/
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member
    Join Date
    Jul 2012
    Posts
    1
    Rep Power
    0
    Quote Originally Posted by zyousafi View Post
    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

  4. #4
    Junior Member
    Join Date
    Jul 2012
    Posts
    6
    Rep Power
    0
    @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.

    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
    Last edited by Admin; 08-06-2012 at 04:35 PM.

  5. #5
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi,

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

    replace

    Code:
    sCC = "zyousafi@test.com" & "zyousafi@test.com" & ";"
    with

    Code:
    sCC = "zyousafi@test.com" & ";" & "zyousafi@test.com" & ";"
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  6. #6
    Junior Member
    Join Date
    Jul 2012
    Posts
    6
    Rep Power
    0
    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?

  7. #7
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Quote Originally Posted by zyousafi View Post
    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.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  8. #8
    Junior Member
    Join Date
    Jul 2012
    Posts
    6
    Rep Power
    0
    thank you

Similar Threads

  1. Replies: 26
    Last Post: 10-22-2019, 02:39 PM
  2. Printing range of sheets in excel or in PDF
    By excel_learner in forum Excel Help
    Replies: 18
    Last Post: 10-11-2015, 01:36 PM
  3. Strange Excel Spinner Buttons Help??
    By stangirl87 in forum Excel Help
    Replies: 2
    Last Post: 10-08-2012, 07:31 AM
  4. Replies: 7
    Last Post: 05-09-2012, 11:34 PM
  5. Replies: 3
    Last Post: 02-20-2012, 12:54 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •