Report in EMail Body (1 Viewer)

Wayne

Crazy Canuck
Local time
Today, 09:20
Joined
Nov 4, 2012
Messages
176
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.

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 Sub

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
 
Hi Wayne. Which line is giving you the error?
 
That's the weird part. It is not telling me. Usually, it will highlight the code that is not working, or that is the problem, but not this time.

Wayne
 
That's the weird part. It is not telling me. Usually, it will highlight the code that is not working, or that is the problem, but not this time.

Wayne
Hi Wayne. Okay, you're saying you're getting an error message about "object required," but it's not offering you a debug mode to show where the error is happening. Is this correct? Have you tried compiling your code? Does it compile?
 
It seems to run okay to the highlighted point.

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
    
    [COLOR="Red"]Do While Not EOF(1)
        Input #1, strLine
        
        strBodyText = strBodyText & strLine & "<br><br>"
    Loop
    Close 1[/COLOR]
       
    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 Sub

I'm stumped.

Wayne
 
Hi Wayne. You're missing the hashtag in the Open statement. Take a look at this blog for a review on how to read a text file into a variable.


PS. When I said you were missing the hashtag (#), I mean you're missing it in more than one place. You need it in the Open statement and in the Do While Loop as well.
 
Okay, I tried what you said, and now I get an error message "Syntax Error" on this line (Highlighted below):

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
    Dim intFile As Integer

    intFile = FreeFile()
        
    '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 #intFile
    
    '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
    
    [B][COLOR="Red"]Do While Not EOF(#intFile)[/COLOR][/B]
        Input #intFile, strLine
        
        strBodyText = strBodyText & strLine & "<br><br>"

    Loop

    Close #intFile
       
    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 Sub

Getting closer to the finish line.

Wayne
 
Hi Wayne. I would try plugging in a literal value for the filepath to open and make sure it exists just to rule out any issues with the code. If it doesn’t work, then you know it’s the code. Otherwise, it’s the file.
 
I would take the html out and go back to the original, to see if it still works.

is the signature an outlook signature, or just another piece of text.

do a msgbox to see what the html body looks like before you insert it into the email. I presume that the problem is outlook doesn't like the format of the email, and not that there is a code error in access. The file index is probably a red herring.
 
This part of the code seems to be the problem. It says I have a syntax error on the first line.

Code:
Do While Not EOF([COLOR="Red"]#[/COLOR]intFile)
        Input #intFile, strLine
        
        strBodyText = strBodyText & strLine & "<br><br>"

    Loop

If I take out the hashtag on the first line, the code generates the HTML report and saves it exactly where I told it to, and it looks fine (the report). Then the code stops and gives me an error message "Object Required", but does not highlight the line where the code has a problem. That is making it difficult for me to figure out what object is required, and where. I have been up and down the forum looking for answers, but no luck.

Yes the signature is an outlook signature, and I traced the file path to it, and it is correct in the code. As I said in my first post, this code worked perfectly to generate an HTML email in Outlook, before I tried to add the Scope of Work report to the body of the email. Somewhere in that added code lies problem. I was just hoping a fresh pair of eyes couldn't hurt.

Thank you for your responses, and any help you can give.

Wayne
 
This part of the code seems to be the problem. It says I have a syntax error on the first line.

Code:
Do While Not EOF([COLOR=Red]#[/COLOR]intFile)
        Input #intFile, strLine
        
        strBodyText = strBodyText & strLine & "<br><br>"

    Loop
If I take out the hashtag on the first line, the code generates the HTML report and saves it exactly where I told it to, and it looks fine (the report). Then the code stops and gives me an error message "Object Required", but does not highlight the line where the code has a problem. That is making it difficult for me to figure out what object is required, and where. I have been up and down the forum looking for answers, but no luck.

Yes the signature is an outlook signature, and I traced the file path to it, and it is correct in the code. As I said in my first post, this code worked perfectly to generate an HTML email in Outlook, before I tried to add the Scope of Work report to the body of the email. Somewhere in that added code lies problem. I was just hoping a fresh pair of eyes couldn't hurt.

Thank you for your responses, and any help you can give.

Wayne
Hi Wayne. Are you able to post a demo copy of your db, so we can give it a try?
 
Answering on my phone so can't check the code.
You have defined intFile as Integer
So you should be referencing intFile and not #intFile
 
Answering on my phone so can't check the code.
You have defined intFile as Integer
So you should be referencing intFile and not #intFile

good point. I never quite understand why you need the # in some file I/O commands (but not others), when the variable is already numeric.
 
As I said in my last reply I'm unable to test your code at the moment.
However, I don't think any of that code section makes sense
EOF is used with a recordset but you don't seem to have defined that anywhere

Then it should be something like Do Until rst.EOF and I believe it should be without the bracketed part
In any case how would the code know it was at EOF if you input intfile afterwards?

Then why do you have "<br><br>"?
Possibly you want strBodyText = strBodyText & strLine & vbCrLf & vbCrLf
Or perhaps strBodyText = strBodyText & strLine & vbTab & vbTab
 
When you are writing the email in HTML, vbCrLf doesn't work. You have to use "<br><br>" to skip two lines.

In any event, I got the code to work. The problem was the signature part. When I put a literal file path and file name, it works. When I used this code:

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

It wouldn't work - it would just stop. I changed it to this, and it works.

Code:
SigString = "C:\Users\wayne\AppData\Roaming\Microsoft\Signatures\Wayne2.htm"
        If Dir(SigString) <> "" Then
            Signature = GetBoiler(SigString) & MyLogo
        Else
            MsgBox "Requested signature does not exist.", vbInformation, "Crate & Pack"
        End If

Also used a Function called "GetBoiler". And now it works.

The only problem now is the HTML formatting of the report in the email body. Somehow it's not coming out the same.

It should be this:

Scope of Work

1 - Repair - Elliptical

Instead I get this:




Scope of Work








1 - Repair - Elliptical

I'm going to play with HTML formatting a bit to see if I can get it right. I would like to thank all of you for your ideas. That's why I love this forum.

Wayne
 
Hi Wayne. Thanks for the update. Good luck!
 
The way I approached it with a HTML signature, was to have a template, then split the template by header and footer (with signature), insert the data and put the template back together again. Not as hard as it sounds.
 
I wonder if the case was significant. Case normally isn't in access but sometimes it IS significant, especially when dealing with external systems.

eg - trying to set a printer directly needs the printer name to have correct case.

so to set the application printer
(not sure about the syntax - just an example)
Code:
[B]set printer = "myprintername"[/B]
this fails if the printer is really called "MyPrinterName"

but this works, because the string comparison is not case sensitive within vba
Code:
for each ptr in application.printers
   [COLOR="Red"]if ptr.name = "myprintername" then[/COLOR]
       set printer = ptr
       exit loop
   end if
next
 
Hey Gasman,

Could you please show me an example of your split signature template? In my database, I have seven different emails to send out, and a template would work better than individual coding.


Wayne
 

Users who are viewing this thread

Back
Top Bottom