Run Time Error 1004 PDF & Outlook

new11

New Member
Joined
Sep 15, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows
Good morning incredible excel people! ?
I hope you’re all going well this sunny weekend.

I was just wondering if someone might be able to help point me in the right direction or if you have any ideas that would be greatly appreciated.

I have the below module that takes two sheets and inserts them as PDF’s into an outlook desktop email. However, I’m getting the “Run Time Error 1004” error thrown at me now. (The code had been working for about 16 months)
There are a couple variations of the error that occur in the following.
  • If I have outlook desktop open looking at my emails, the error appears
  • If the PC is restarted without opening outlook desktop this time, the error disappears.
    • However, the email does not display on screen, as it is supposed to allowing me to check and add a few more items before I send
I’m also getting something completely new that might be connected to “Run Time Error 440”
  • Run time error 2147287038 (80030002)
    • We can’t open [the email oft]. It’s possible the file is already open, or you don't have permission to open it.
I’m probably missing something really obvious but more heads is better than one. ?
Many thanks in advance for any assistance or ideas!
Ps. Apologies for any obvious mistakes in my code, I VBA coding skill are super limited.

VBA Code:
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top