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
