Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Excel 2010 - worksheet to pdf, display outlook email save to sepcific folder

  1. #1

    Excel 2010 - worksheet to pdf, display outlook email save to sepcific folder

    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hey there, I'm new to posting here, I've usually been able to search for my solutions and you all are a great resource!

    My current issue:

    I'm using Excel 2010. I'm creating a file for 30 different users to use (can't use add-ins or plug-ins)- they will each save their own copy of it, will not use the same file.

    I need some code that will do the following:
    • Save a specific range on the current worksheet as a .pdf, based on a name in a cell on a different worksheet (can add the formula to current worksheet if needed) into a specific directory other than the location of the current file (the formula will cause the file name to save with the date as part of the file name to keep a record of all the pdf versions emailed by date)
    • Attach the file to an email in Outlook as a pdf (most code I'm finding attaches it as a .xlsx file) with a specific subject, adds the users current signature for a new email to the body but leaves all other fields to be completed by user (to:, cc:, bcc:, etc), display the email for the user to modify prior to sending

    I seem to be able to find bits and pieces of what I need but can't seem to get it all to work together with Excel 2010. Any help is greatly appreciated!

  2. #2
    Test this file and see if it will work.

    You need to Create a folder named Saved PDF on C:\

    C:\Saved PDF (This is folder where the code is set to save the PDF file into.)

    Data in the Range A10:I15is what gets saved into the PDF file. ( This range can be adjusted)

    The following items can be adjusted in the code that shows up in the Email

    Text in Body of Email
    Attached Files Attached Files

  3. #3
    It does not work. It fails at
    Set OutApp = CreateObject("Outlook.Application")

    Which is where it fails in my current code (wrote all of this last night after no response, based on what I know):
    Sub ArchiveandPublish()
      Dim bStarted As Boolean
      Dim oOutlookApp As Outlook.Application
      Dim oItem As Outlook.MailItem
      'Dims for Outlook function
      Dim rng As Range
      Set rng = Range("B1:N41")
      'SetupRangeData rng
      Dim filePath As String
      ' Change this file path to the folder where the file will be saved:
      filePath = "foldername (had to remove due to link issues)"
      Dim fileName As String
      ' Sets the file name to a specific cell in the active worksheet
      fileName = ActiveCell.Value
      MsgBox fileName 'only used to check the file path name, can comment out after testing
      Dim f As String
      'This combines the file path, file name and makes the file a pdf
      f = filePath & fileName & ".pdf"
      rng.ExportAsFixedFormat Type:=xlTypePDF, _
       fileName:=f, Quality:=xlQualityStandard, _
       IncludeDocProperties:=True, IgnorePrintAreas:=True, _
       From:=1, To:=1, OpenAfterPublish:=True
       'This exports it as a pdf and sets the format of the document
    On Error Resume Next 'put in to test rest of code despite error with attachging to outlook
    'Start outlook if not open
    Set oOutlookApp = GetObject(, "Outlook.Application")
    If Err <> 0 Then
         Set oOutlookApp = CreateObject("Outlook.Application")
         bStarted = True
    End If
    Set oItem = oOutlookApp.CreateItem(olMailItem)
    With oItem
         .Subject = "Rateguide test"
         'Add the document as an attachment, you can use the .displayname property
         'to set the description that's used in the message
        .Attachments.Add Source:=f, Type:=olByValue, _
           DisplayName:="Document as attachment"
        .Display 'create email in DISPLAY format to allow user to edit email before sending. Change .Display to .Send to send automatically
    End With
    End Sub
    The above code works on my co workers machine, but does not work on mine. Is there an Outlook setting I'm missing? or something else?

  4. #4
    In the visual basic editor goto tool ->References and compare the references you have checked to what your co-worker machine has. My first guess would be you do not have an object library checked in References.

  5. #5
    We checked references last night and I have all the same references including object references checked.

  6. #6
    I had 3 basic users test it (people who would have never touched their references) and it works on their machines just fine.

  7. #7
    Here is my take on it , do with the information what you want.

    I can assure you that the code in my file is good. You tested your code in several other machines as well and it works fine. So you don't have a code issue. It seems resonable to me to believe you have an issue with the setup on your machine.

    There is some in depth info on microsoft support that explains why this very error shows up on some machines. even when it is an exact setup as a machine that will run the code without errors.

    one of the things Microsoft recommends is to re-install office. There other things or issues that can be checked out but if it was me this is what i would try first. again just my opinion do with it what you want.

  8. #8
    Thank you I will try that *sigh* gotta love computers

  9. #9
    Seeker joseph4tw's Avatar
    Join Date
    May 2012
    South Florida, USA
    This may sound silly, but have you tried running the code while Outlook was open?

    Also, I would try doing a "Repair" on MS Office before I would do a full uninstall/re-install. It might save you some time.

  10. #10
    Update: I did the repair, that didn't work, but reinstalling office fixed it. It won't work on my computer if Outlook isn't running, but it does work on other machines (by opening OUtlook via the code). I guess it is what it is, it's weird.

Page 1 of 2 1 2 LastLast

Posting Permissions

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