This one has been a real head-scratcher for me. I keep getting and error message saying "Object required". I have been over and over this a hundred times but can't see the error. Any help would be appreciated.
The email code was working fine, until I tried adding the HTML report into the body of the email. Maybe a fresh pair of eyes will help.
Thanks in advance.
Wayne
Code:
Private Sub btnEMailTechnician_Click()
On Error GoTo Err_btnEMailTechnician_Click
'Define some object variables for Outlook
Dim olApp As Outlook.Application
'The NameSpace object allows you to reference folders
Dim olNS As Outlook.NameSpace
Dim olFolder As Outlook.MAPIFolder
'Create a reference to the email item you will use to send your email message
Dim olMailItem As Outlook.MailItem
Dim strBodyText As String
Dim strEMail As Variant
Dim Signature As Outlook.Items
Dim strPathWorkOrders As String
Dim strPathTempFiles As String
Dim strAddress As String
Dim strPhone As String
Dim strLine As String
'Create the Outlook Object
Set olApp = CreateObject("Outlook.Application")
Set olNS = olApp.GetNameSpace("MAPI")
Set olFolder = olNS.GetDefaultFolder(olFolderInbox)
Set olMailItem = olFolder.Items.Add("IPM.Note")
'Create the string for the email address
strEMail = DLookup("[EmpEmailAddress]", "tblEmployees", "[EmpFullName] = '" & Me.HelperTechnician1 & "'")
If IsNull(Me.ClientAptNumber) Then
strAddress = "<html><font face=Calibri><font size=3>" & _
(Me.ClientStreetAddress & "," & "<br>" & vbCrLf & _
Me.ClientCityName & ", " & Me.ClientState & " " & Me.ClientZipCode & ".")
Else
strAddress = "<html><font face=Calibri><font size=3>" & _
("# " & Me.ClientAptNumber & " - " & Me.ClientStreetAddress & "," & "<br>" & vbCrLf & _
Me.ClientCityName & ", " & Me.ClientState & " " & Me.ClientZipCode & ".")
End If
If IsNull(Me.ClientPhone2) Then
strPhone = "<html><font face=Calibri><font size=3>" & _
(Me.ClientPhone1 & " (" & Me.ClientPhoneType1 & ")")
Else
strPhone = "<html><font face=Calibri><font size=3>" & _
(Me.ClientPhone1 & " (" & Me.ClientPhoneType1 & ")" & "<br>" & vbCrLf & _
Me.ClientPhone2 & " (" & Me.ClientPhoneType2 & ")")
End If
'Create the string for the Work Orders Directory Path
strPathWorkOrders = DLookup("[WorkOrdersDirectoryPath]", "tblSysConfig", "[CompanyName] = '" & [Forms]![frmOrders]![CompanyName] & "'")
'Create the string for the Temp Folder for the report to go to
strPathTempFiles = DLookup("[TempFilePath]", "tblSysConfig", "[CompanyName] = '" & [Forms]![frmOrders]![CompanyName] & "'")
'Create the body text report
DoCmd.OutputTo acOutputReport, "rptServiceDetails", acFormatHTML, strPathTempFiles & "\Order Details - " & Me.OrderNumber & _
" - " & Me.ClientUserlastName & ".html"
'Create the signature for the email
Set Signature = Environ("appdata") & "\Roaming\Microsoft\Signatures\"
If Dir(Signature, vbDirectory) <> vbNullString Then
Signature = Signature & Dir$(Signature & "Wayne.htm")
Else
Signature = ""
End If
Signature = CreateObject("Scripting.FileSystemObject").GetFile(Signature).OpenAsTextStream(1, -2).ReadAll
'Open the report
Open strPathTempFiles & "\Order Details - " & Me.OrderNumber & " - " & Me.ClientUserlastName & ".html" For Input As 1
'Create the body of the message from the data in the form
If IsNull(Me.ClientNotes) Then
strBodyText = "<html><font face=Calibri><font size=3>" & _
"As a technician assigned to this job, here is a copy of Work Order for " & Me.ClientUserlastName & ", " & Me.ClientUserFirstName & " - Service Date set for " & _
Format(Me.ServiceDate, "mmmm, dd, yyyy") & "." & "<br>" & vbCrLf & _
"Currently booked for arrival/start time of " & Format(Me.ApptTime, "h:mm AMPM") & " - " & _
Format(Me.ApptTimeEnd, "h:mm AMPM") & "." & "<br><br>" & vbCrLf & vbCrLf & _
"<b><u>" & "Job Address:" & "</b></u><br>" & vbCrLf & _
strAddress & "<br>" & vbCrLf & strPhone & "<br><br>" & vbCrLf & vbCrLf
Else
strBodyText = "<html><font face=Calibri><font size=3>" & _
"As a technician assigned to this job, here is a copy of Work Order for " & Me.ClientUserlastName & ", " & Me.ClientUserFirstName & " - Service Date set for " & _
Format(Me.ServiceDate, "mmmm, dd, yyyy") & "." & "<br>" & vbCrLf & _
"Currently booked for arrival/start time of " & Format(Me.ApptTime, "h:mm AMPM") & " - " & _
Format(Me.ApptTimeEnd, "h:mm AMPM") & "." & "<br><br>" & vbCrLf & vbCrLf & _
"<b><u>" & "Job Address:" & "</b></u><br>" & vbCrLf & _
strAddress & "<br>" & vbCrLf & strPhone & "<br><br>" & vbCrLf & vbCrLf & _
"<u><b>" & "Special Notes:" & "</u></b>" & " " & Me.ClientNotes & "<br><br>" & vbCrLf & vbCrLf
End If
Do While Not EOF(1)
Input #1, strLine
strBodyText = strBodyText & strLine & "<br><br>"
Loop
Close 1
If Len(Dir(strPathWorkOrders & "\" & Me.OrderNumber & " " & _
Me.ClientUserlastName & " POD" & ".pdf")) = 0 Then
MsgBox "The Work Order you are trying to attach does not exist in the directory selected.", vbInformation
Exit Sub
End If
If IsNull(Me.ApptTime) Then
MsgBox "You haven't entered an appointment start time. You must have a start time.", vbInformation
Exit Sub
Else
'Update the new email object with the form data
With olMailItem
.Subject = Me.OrderNumber & " - " & Me.ClientUserlastName & ", " & Me.ClientUserFirstName
.To = Replace(Mid(strEMail, InStr(1, strEMail, ":") + 1), "#", "")
.BCC = "myemail"
.ReadReceiptRequested = True
.HTMLBody = strBodyText & Signature
.Importance = olImportanceHigh
.Display
.Attachments.Add strPathWorkOrders & "\" & Me.OrderNumber & " " & _
Me.ClientUserlastName & " POD" & ".pdf"
End With
End If
'Release all of the object variables
Set olMailItem = Nothing
Set olFolder = Nothing
Set olNS = Nothing
Set olApp = Nothing
Exit_btnEMailTechnician_Click:
Exit Sub
Err_btnEMailTechnician_Click:
MsgBox Err.Description, vbInformation, "Error"
Resume Exit_btnEMailTechnician_Click
End SubThe email code was working fine, until I tried adding the HTML report into the body of the email. Maybe a fresh pair of eyes will help.
Thanks in advance.
Wayne
