I am using the code below to generate automatic e-mails from a form in Access. I had so many issues with the Outlook automation due to system differences, so I decided to use the approach mentioned in FAQ705-537. It works fine, except when it populates the e-mail, it will not put in a return (new line) in the txtbody where indicated. Anyone know how to get the generated e-mail to format properly?

Module Code
Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Command Buttom Code
On Error GoTo Err_cmdEMail_Click

    Dim stext As String
    Dim saddedtext As String
    Dim txtsubject As String
    Dim txtbody As String

    With frm_Action_Item

        txtsubject = "Action Item Status for Item No. TSF-05-" & Me.Item_ID

        txtbody = "Action Item Status for Item No. TSF-05-" & Me.Item_ID & vbCrLf & _
        "Description: " & Me.Item_Description & vbCrLf & _
        "Derived From: " & Me.Derived_From & vbCrLf & _
        "Due Date: " & Me.Date_Due & vbCrLf & _
        "Response Type: " & Me.Response_Type & vbCrLf & _
        "Comments: " & Me.Comments & vbCrLf

    End With

    If Len(txtMainAddresses) Then
        stext = txtMainAddresses
    End If
    If Len(txtCC) Then
        saddedtext = saddedtext & "&CC=" & txtCC
    End If
    If Len(txtBCC) Then
        saddedtext = saddedtext & "&BCC=" & txtBCC
    End If
    If Len(txtsubject) Then
        saddedtext = saddedtext & "&Subject=" & txtsubject
    End If
    If Len(txtbody) Then
        saddedtext = saddedtext & "&Body=" & txtbody
    End If
    If Len(txtAttachment) Then
        saddedtext = saddedtext & "Attach=" & Chr$(34) & txtAttachment & Chr$(34)
    End If

        stext = "mailto:" & stext

    If Len(saddedtext) <> 0 Then
        Mid$(saddedtext, 1, 1) = "?"
    End If

    stext = stext & saddedtext

    ' launch default e-mail program
    If Len(stext) Then
        Call ShellExecute(hwnd, "open", stext, vbNullString, vbNullString, SW_shownormal)
    End If

Exit_cmdEMail_Click:
    Exit Sub

Err_cmdEMail_Click:
    MsgBox Err.Description
    Resume Exit_cmdEMail_Click

End Sub

Which part of the txtbody declaration is not working?

Curious: What does "If Len(txtCC) Then" and other similar statements mean to you? To me, you need to compare it to something, i.e. "If Len(txtCC) = 0 Then" or "If Len(txtCC) > 0 Then", etc. You're not comparing them to anything.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

The txt txtbody portion does populate the body of the e-mail, but it does not add new lines where vbCrLf is indicated. It runs it all together.

For your second question, I copied this code from another source and the only other info I have is it stated that “This will send To, CC and BC as well as Subject, Message, and Attachment
You need 6 text boxes or variables which match the following:
txtMainAddresses
txtCC
txtBCC
txtSubject
txtBody
txtAttachment”
I am new at programming, so I wish I could provide more information. It does generate an e-mail though.

Thanks in advance for you help.

MAB2005

So the function vbCrLf is not working? Which version of Access are you using? Perhaps one of your libraries is missing. Open any code and click TOOLS > REFERENCES and check for any of them having the word "Missing" next to it.

If all looks good, try replacing vbCrLf with the combination of CHR(13) & CHR(10), e.g.

txtbody = "Action Item Status for Item No. TSF-05-" & Me.Item_ID & Chr(13) & Chr(10) & _
etc.



Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Thanks for the recommendation, but unfortunately got the same result. All the references are fine. I even tried using "<BR>" (which someone else suggested) since it is an html e-mail, but it just puts it in the text. Not sure what else to do. In the meantime, I included semi-colons in the text body, so it provides some separation of the items.

OK, desperation time. Try this combo

& vbCRLF & vbCRLF (two in a row)

or

& vbCRLF & " " & vbCRLF (line, space, line)

Wacky... but either may "force" the computer to "wake up."


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Thanks again, but I am still getting the same result. I think I will just go with the semi-colons. Appreciate your effort. MAB2005

re: If Len(txt)then ..

This is how it was explained to me once, and it appears to work.

The If statement tests a condition, the result being True or False - so far nothing startling! However, to a computer True and False are just representations of the numbers 0 and 1, so 'If len(txt)then ...' is exactly then same as saying 'If Len(txt) = 0 then ...'

To be honest, I'd rather see the full statement as it makes debugging so much easier, just like 'If Not .NoMatch then ...' is better as 'If .NoMatch = False then ...', but that's the joy of VBA programming - ask 5 programmers and you'll get 10 different approaches - all of which could be right.

OK, that makes sense about "If Len(txt) Then" being the same as "If Len(txt) = 0 Then" when the length is zero. It resolves to false. However, false is zero and true is -1. The length is never -1. So you can never use this construct, "If Len(txt) Then" for TRUE unless you include an ELSE, that is

If Len(txt) Then
    Do this for FALSE 'i.e. Len(txt) = 0
Else
    Do that for TRUE
End If

Or with a NOT, i.e. "If Not Len(txt) Then"

It's not quite exactly like a boolean (or checkbox) where you can do this "If Me.fMyCheckBox Then" because a checkbox can be TRUE or FALSE (barring null state).

Because it's good practice to resolve TRUEs before FALSEs in IF/THEN/ELSE constructs, I wouldn't use the "If Len(txt) Then" because it forces me to put the FALSE action before the TRUE.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

The answer to my question was solved by someone. Below is what needs to be used.

& "%0D%0A" (for return)
& "%0D%0A" & "%0D%0A" & _ (for new line and space)