Email formatting when creating in Access VBA

[Isskint]

Hello community

I have an issues tracking database where the operator can email the ticket history (using ColumnHistory from a LongText field) to relevant persons. Initially I used a macro using the EMailDatabaseObject action. This works fine but then they needed to add attachments. I have achieved this via VBA easily enough. However, the ticket history appears as one continuous line rather than a new line for each [Version History].
So via EMailDatabaseObject i get:

Ticket History

[Version: 17/12/2013 10:09:31 ] yadaddada
[Version: 17/12/2013 13:01:55 ] yadaddada
[Version: 17/12/2013 13:02:53 ] yadaddada

but with VBA i get:

Ticket History[Version: 17/12/2013 10:09:31 ] yadaddada[Version: 17/12/2013 13:01:55 ] yadaddada[Version: 17/12/2013 13:02:53 ] yadaddada

Can anyone identify why and can i get around this?

 

[pr2-eugin]

Good to see you Isskink, would you mind sharing the VBA Code which you use to send the email?

 

[Isskint]

Hi Paul - I have been absent for a while, haven't I

Public Sub UpdateEmail()

Dim olApp As Object
Dim objMail As Object
Dim objAttachments As Outlook.Attachment
Dim rstCurrent As DAO.Recordset
Dim FieldName As String
Dim rstChild As DAO.Recordset2
Dim strTempLoad As String
Dim fldAttach As DAO.Field2

Set rstCurrent = CurrentDb.OpenRecordset("Issues")
FieldName = "Attachments"

On Error Resume Next 'Keep going if there is an error


Set olApp = GetObject(, "Outlook.Application") 'See if Outlook is open


If Err Then 'Outlook is not open
Set olApp = CreateObject("Outlook.Application") 'Create a new instance of Outlook
End If

'Create e-mail item
Set objMail = olApp.CreateItem(olMailItem)

With objMail

    'Set body format to HTML
    .BodyFormat = olFormatHTML
    .To = EmailList
    .Subject = "Status update - " & Replace(Replace("Issue |1: |2", "|1", Nz(Forms![Issue Details].[ID], "")), "|2", Nz(Forms![Issue Details].[Title], ""))
    .HTMLBody = "Ticket History" & Chr(13) & ColumnHistory("Issues", "Comments", "[ID]=" & Nz(Forms![Issue Details].[ID], 0))
    
    rstCurrent.MoveFirst
    Do Until rstCurrent.EOF
        If rstCurrent.Fields("ID") = Forms![Issue Details].ID Then
            Set rstChild = rstCurrent.Fields(FieldName).Value ' the .Value for a complex field returns the underlying Recordset.
            rstChild.MoveFirst
            
            Do Until rstChild.EOF
                If Right(rstChild.Fields("FileName").Value, 3) = "msg" Then
                    .Attachments.Add ("S:\Warehouse\SNAP issues\Emails\" & rstChild.Fields("FileName").Value)
                ElseIf IsImage(Right(rstChild.Fields("FileName").Value, 3)) Then
                    .Attachments.Add ("S:\Warehouse\SNAP issues\Images\" & rstChild.Fields("FileName").Value)
                Else
                    .Attachments.Add ("S:\Warehouse\SNAP issues\Documents\" & rstChild.Fields("FileName").Value)
                End If
                    
                rstChild.MoveNext
                    
            Loop
        End If
        rstCurrent.MoveNext
    Loop
    
    .Display

End With
End Sub

EmailList is a function that creates the To string from name selected in a combobox.
IsImage is a function that returns True is the file type is of an image type.

 

[pr2-eugin]

Hi Paul - I have been absent for a while, haven't I

Yes, quiet a while Mark.
I think the problem lies in the ColumnHistory function. See if the String concatenation is using a line break.

Also use vbCrLf or Chr(10) & Chr(13). Using Chr(13) on its own does not always work IMVHO.

 

[Isskint]

Thanks Paul,

I have tried the options on CHR(13) but that has not made any changes to having the heading Ticket History on a separate line.

I have trawled the net on ColumnHistory but can not find anything to explain how each entry is added to the previous - line feed, carriage return !!??

 

[Chrisopia]

is the email plain text or HTML?
I have had to set my body text with basic html tags, such as <font size=12pt> and <b> ...
It may be worth using <p> ... </p> tags, or <br/> tags within the bodytext code??

 

[Isskint]

Chrisopia thank you

I have tried both ways with no difference sadly.

Using HTML tags may give me the line break between the heading Ticket History and the ColumnHistory - i will give that a try next week.

Perhaps i am looking at this form the wrong angle. It works fine with the EMailDatabaseObject action from a macro but not when using the CreateItem(olMailItem) method from VBA. Does Access do something different when attaching ColumnHistory within the macro?