VBA PDF Run Time Error 1004

%3CLINGO-SUB%20id%3D%22lingo-sub-2690599%22%20slang%3D%22en-US%22%3EVBA%20PDF%20Run%20Time%20Error%201004%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2690599%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20guys%2C%3C%2FP%3E%3CP%3EI%20hope%20you're%20all%20going%20well.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EJust%20a%20quick%20question%3B%20does%20anyone%20know%20how%20I%20might%20be%20able%20to%20fix%20this%20error%20that%20appears%20when%20I'm%20trying%20to%20attach%20two%20temp%20PDF%20files%20into%20an%20email%20from%20excel%3F%3CBR%20%2F%3EThe%20error%20appears%20if%20I%20have%20outlook%20desktop%20open.%20However%2C%20If%20I%20restart%20the%20PC%20without%20opening%20outlook%2C%20the%20error%20disappears%20but%20the%20email%20does%20not%20display%20on%20screen%20so%20I%20can%20check%20before%20sending.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI'll%20place%20the%20code%20into%20the%20bottom%20of%20this%20OP%3B%20PS%20apologies%20if%20I'm%20missing%20anything%20obvious.%20My%20VBA%20skills%20are%20very%20limited.%20Many%20thanks%20in%20advance%20for%20any%20assistance%20or%20ideas.%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3ESub%20emailOFTupdaated()%0ADim%20otlApp%20As%20Object%0A%20%20%20%20Set%20otlApp%20%3D%20CreateObject(%22Outlook.Application%22)%0ADim%20otlNewMail%20As%20Object%0ASet%20otlNewMail%20%3D%20otlApp.CreateItemFromTemplate(%22C%3A%5CUsers%5Cwilli%5CDesktop%5CAug_2021_Tenent_Direct.oft%22)%0A%20%20%20%20With%20otlNewMail%0A%20%20%20%20vTemplateBody%20%3D%20otlNewMail.HTMLbody%0A%20%20%20%20vTemplateSubject%20%3D%20otlNewMail.Subject%0A%20%20%20%20CatEmail%20%3D%20otlNewMail.Categories%20%3D%20%22Rental%20(%20St)%22%0A%20%20%20%20Sensitivity%20%3D%20otlNewMail.Sensitivity%0A%20%20%20%20OutAccount%20%3D%20otlApp.Session.Accounts.Item(2)%0A%20%20%20%20'.Close%201%0A%20%20%20%20End%20With%0A%20%20%20%20%0ADim%20Billing_Invoice%20As%20Worksheet%0A%20%20%20%20Set%20Billing_Invoice%20%3D%20ActiveWorkbook.Worksheets(%22Billing_Invoice%22)%0ADim%20Power_Manager%20As%20Worksheet%0A%20%20%20%20Set%20Power_Manager%20%3D%20ActiveWorkbook.Worksheets(%22Power%20Manager%22)%0ADim%20Password%20As%20String%0A%20%20%20%20Password%20%3D%20Split(Power_Manager.Range(%22N11%22).Value%2C%20%22%20%22)(0)%0ADim%20Receipt%20As%20Worksheet%0A%20%20%20%20Set%20Receipt%20%3D%20ActiveWorkbook.Worksheets(%22Receipt%22)%0A%0A%20%20%20%20Billing_Invoice.Unprotect%20Password%0A%20%20%20%20Power_Manager.Unprotect%20Password%0A%20%20%20%20Receipt.Unprotect%20Password%0A%20%20%20%20%0ADim%20Y%20As%20Double%0ADim%20X%20As%20Double%0A%20%20%20%20Y%20%3D%20DateValue(Now)%0A%20%20%20%20X%20%3D%20TimeValue(Now)%0ADim%20strPath%20As%20String%0A%20%20%20%20strPath%20%3D%20Environ%24(%22temp%22)%20%26amp%3B%20%22%5C%22%0ADim%20strFName2%20As%20String%0A%20%20%20%20strFName2%20%3D%20%22_ST%23%7B%22%20%26amp%3B%20(Billing_Invoice.Range(%22J20%22).Value)%20%26amp%3B%20%22%7D%22%20%26amp%3B%20%22_%22%20%26amp%3B%20Y%20%26amp%3B%20%22_%22%20%26amp%3B%20X%20%26amp%3B%20%22.pdf%22%0A%0ADim%20strFName3%20As%20String%0A%20%20%20%20strFName3%20%3D%20%22Payment_Receipt%22%20%26amp%3B%20%22-%22%20%26amp%3B%20%22Invoice%23%7B%22%20%26amp%3B%20(Billing_Invoice.Range(%22J20%22).Value)%20%26amp%3B%20%22%7D%22%20%26amp%3B%20%22.pdf%22%0A%0ADim%20invoice_nr%20As%20String%0A%20%20%20%20invoice_nr%20%3D%20Billing_Invoice.Range(%22j20%22).Value%0ADim%20issue_date%20As%20String%0A%20%20%20%20issue_date%20%3D%20Billing_Invoice.Range(%22I25%22).Value%0ADim%20billing_month%20As%20String%0A%20%20%20%20billing_month%20%3D%20Billing_Invoice.Range(%22I25%22).Value%0A%20%20%20%20Billing_Invoice.Range(%22I25%22).Value%20%3D%20billing_month%0A%20%20%20%20billing_month%20%3D%20Format(Date%2C%20%22mmm%20yyyy%22)%0A%20%20%20%20%0A%20%20%20%20Billing_Invoice.ExportAsFixedFormat%20Type%3A%3DxlTypePDF%2C%20Filename%3A%3DstrPath%20%26amp%3B%20strFName2%2C%20Quality%3A%3DxlQualityStandard%2C%20IncludeDocProperties%3A%3DTrue%2C%20IgnorePrintAreas%3A%3DFalse%2C%20OpenAfterPublish%3A%3DFalse%20%0A%20%20%20%20Receipt.ExportAsFixedFormat%20Type%3A%3DxlTypePDF%2C%20Filename%3A%3DstrPath%20%26amp%3B%20strFName3%2C%20Quality%3A%3DxlQualityStandard%2C%20IncludeDocProperties%3A%3DTrue%2C%20IgnorePrintAreas%3A%3DFalse%2C%20OpenAfterPublish%3A%3DFalse%0A%0AOn%20Error%20GoTo%200%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20ActiveWorkbook.RefreshAll%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0AOn%20Error%20Resume%20Next%0A%0ASet%20otlApp%20%3D%20CreateObject(%22Outlook.Application%22)%0ASet%20otlNewMail%20%3D%20otlApp.CreateItem(0)%0AWith%20otlNewMail%0A%20%20%20%20.To%20%3D%20%22%3CREMOVED%3E%22%0A%20%20%20%20.BCC%20%3D%20%22%3CREMOVED%3E%22%0A%20%20%20%20.Subject%20%3D%20%222%20%3CREMOVED%3E%20ST%20%7C%20Automatic%20Utility%23%20%22%20%26amp%3B%20%22(%22%20%26amp%3B%20invoice_nr%20%26amp%3B%20%22)%22%20%26amp%3B%20%22%20Period%20%22%20%26amp%3B%20billing_month%20%26amp%3B%20%22%20%22%20%26amp%3B%20%22%7B%22%20%26amp%3B%20X%20%26amp%3B%20%22-%22%20%26amp%3B%20Y%20%26amp%3B%20%22%7D%22%0A%20%20%20%20.Sensitivity%20%3D%202%0A%20%20%20%20.Categories%20%3D%20(%22Rental%20(%3CREMOVED%3E%20St)%3BGreen%22)%0A%20%20%20%20.Body%20%3D%20olFormatHTML%0A%20%20%20%20.BodyFormat%20%3D%20olFormatHTML%0A%20%20%20%20.HTMLbody%20%3D%20vTemplateBody%0A%20%20%20%20.Attachments.Add%20strPath%20%26amp%3B%20strFName2%20%26amp%3B%20strPath%20%26amp%3B%20strFName3%0A%20%20%20%20.SendUsingAccount%20%3D%20OutAccount%0A%20%20%20%20.Display%20%20%0A%20%20%20%20'.Send%20%20%20%20%20%20'To%20send%20Email%20%0AEnd%20With%0A%20%20%20%20On%20Error%20GoTo%200%0A%20%20%20%20Set%20temp1%20%3D%20Nothing%0A%20%20%20%20Set%20OutApp%20%3D%20Nothing%0A%20%20%20%20Set%20OutAccount%20%3D%20Nothing%0A%20%20%20%20Application.CutCopyMode%20%3D%20False%0A%20%20%20%20%0A%20%20%20%20Power_Manager.Protect%20Password%0A%20%20%20%20Billing_Invoice.Protect%20Password%0A%20%20%20%20Email.Protect%20Password%0A%20%20%20%20Receipt.Protect%20Password%0A%20%20%20%20Application.GoTo%20Reference%3A%3DSheets(%22Power%20Manager%22).Range(%22A1%22)%2C%20Scroll%3A%3DTrue%0AEnd%20Sub%0A%0ASub%20Which_Account_Number()%0A'Don't%20forget%20to%20set%20a%20reference%20to%20Outlook%20in%20the%20VBA%20editor%0A%20%20%20%20Dim%20OutApp%20As%20Outlook.Application%0A%20%20%20%20Dim%20I%20As%20Long%0A%20%20%20%20Set%20OutApp%20%3D%20CreateObject(%22Outlook.Application%22)%0A%20%20%20%20For%20I%20%3D%201%20To%20OutApp.Session.Accounts.Count%0A%20%20%20%20%20%20%20%20MsgBox%20OutApp.Session.Accounts.Item(I)%20%26amp%3B%20%22%20%3A%20This%20is%20account%20number%20%22%20%26amp%3B%20I%0A%20%20%20%20Next%20I%0AEnd%20Sub%3C%2FREMOVED%3E%3C%2FREMOVED%3E%3C%2FREMOVED%3E%3C%2FREMOVED%3E%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2690599%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20Scripts%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Visitor

Hey guys,

I hope you're all going well.


Just a quick question; does anyone know how I might be able to fix this error that appears when I'm trying to attach two temp PDF files into an email from excel?
The error appears if I have outlook desktop open. However, If I restart the PC without opening outlook, the error disappears but the email does not display on screen so I can check before sending.


I'll place the code into the bottom of this OP; PS apologies if I'm missing anything obvious. My VBA skills are very limited. Many thanks in advance for any assistance or ideas.

 

 

Sub emailOFTupdaated()
Dim otlApp As Object
    Set otlApp = CreateObject("Outlook.Application")
Dim otlNewMail As Object
Set otlNewMail = otlApp.CreateItemFromTemplate("C:\Users\willi\Desktop\Aug_2021_Tenent_Direct.oft")
    With otlNewMail
    vTemplateBody = otlNewMail.HTMLbody
    vTemplateSubject = otlNewMail.Subject
    CatEmail = otlNewMail.Categories = "Rental ( St)"
    Sensitivity = otlNewMail.Sensitivity
    OutAccount = otlApp.Session.Accounts.Item(2)
    '.Close 1
    End With
    
Dim Billing_Invoice As Worksheet
    Set Billing_Invoice = ActiveWorkbook.Worksheets("Billing_Invoice")
Dim Power_Manager As Worksheet
    Set Power_Manager = ActiveWorkbook.Worksheets("Power Manager")
Dim Password As String
    Password = Split(Power_Manager.Range("N11").Value, " ")(0)
Dim Receipt As Worksheet
    Set Receipt = ActiveWorkbook.Worksheets("Receipt")

    Billing_Invoice.Unprotect Password
    Power_Manager.Unprotect Password
    Receipt.Unprotect Password
    
Dim Y As Double
Dim X As Double
    Y = DateValue(Now)
    X = TimeValue(Now)
Dim strPath As String
    strPath = Environ$("temp") & "\"
Dim strFName2 As String
    strFName2 = "_ST#{" & (Billing_Invoice.Range("J20").Value) & "}" & "_" & Y & "_" & X & ".pdf"

Dim strFName3 As String
    strFName3 = "Payment_Receipt" & "-" & "Invoice#{" & (Billing_Invoice.Range("J20").Value) & "}" & ".pdf"

Dim invoice_nr As String
    invoice_nr = Billing_Invoice.Range("j20").Value
Dim issue_date As String
    issue_date = Billing_Invoice.Range("I25").Value
Dim billing_month As String
    billing_month = Billing_Invoice.Range("I25").Value
    Billing_Invoice.Range("I25").Value = billing_month
    billing_month = Format(Date, "mmm yyyy")
    
    Billing_Invoice.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strPath & strFName2, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False 
    Receipt.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strPath & strFName3, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

On Error GoTo 0
    Application.ScreenUpdating = False
    ActiveWorkbook.RefreshAll
    Application.ScreenUpdating = True
    Application.ScreenUpdating = True
On Error Resume Next

Set otlApp = CreateObject("Outlook.Application")
Set otlNewMail = otlApp.CreateItem(0)
With otlNewMail
    .To = "<REMOVED>"
    .BCC = "<REMOVED>"
    .Subject = "2 <REMOVED> ST | Automatic Utility# " & "(" & invoice_nr & ")" & " Period " & billing_month & " " & "{" & X & "-" & Y & "}"
    .Sensitivity = 2
    .Categories = ("Rental (<REMOVED> St);Green")
    .Body = olFormatHTML
    .BodyFormat = olFormatHTML
    .HTMLbody = vTemplateBody
    .Attachments.Add strPath & strFName2 & strPath & strFName3
    .SendUsingAccount = OutAccount
    .Display  
    '.Send      'To send Email 
End With
    On Error GoTo 0
    Set temp1 = Nothing
    Set OutApp = Nothing
    Set OutAccount = Nothing
    Application.CutCopyMode = False
    
    Power_Manager.Protect Password
    Billing_Invoice.Protect Password
    Email.Protect Password
    Receipt.Protect Password
    Application.GoTo Reference:=Sheets("Power Manager").Range("A1"), Scroll:=True
End Sub

Sub Which_Account_Number()
'Don't forget to set a reference to Outlook in the VBA editor
    Dim OutApp As Outlook.Application
    Dim I As Long
    Set OutApp = CreateObject("Outlook.Application")
    For I = 1 To OutApp.Session.Accounts.Count
        MsgBox OutApp.Session.Accounts.Item(I) & " : This is account number " & I
    Next I
End Sub

 

 

0 Replies