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

FunnyJt

New member
Joined
Oct 16, 2012
Messages
7
Reaction score
0
Points
0
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!
 
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

Subject
Text in Body of Email
Signature
 

Attachments

  • Save_Mail_PDF.xlsm
    27.2 KB · Views: 102
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):
Code:
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
  Range("A1").Select
  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
     '.To = "ENTER SPECIFIC EMAIL ADDRESS HERE"
     .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?
 
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.
 
We checked references last night and I have all the same references including object references checked.
 
I had 3 basic users test it (people who would have never touched their references) and it works on their machines just fine. :(
 
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.
 
Thank you I will try that *sigh* gotta love computers :)
 
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.
 
Back
Top