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.
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.
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
- 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.
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