Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 19th, 2005, 02:00 PM
Registered User
 
Join Date: Jul 2005
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default (Access/VBA) E-Mail Automation Format

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


 
Old July 19th, 2005, 03:44 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
 
Old July 20th, 2005, 09:03 AM
Registered User
 
Join Date: Jul 2005
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


 
Old July 20th, 2005, 03:31 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
 
Old July 21st, 2005, 11:10 AM
Registered User
 
Join Date: Jul 2005
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

 
Old July 21st, 2005, 12:24 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
 
Old July 22nd, 2005, 02:06 PM
Registered User
 
Join Date: Jul 2005
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old July 26th, 2005, 11:36 AM
Authorized User
 
Join Date: Jul 2004
Posts: 46
Thanks: 0
Thanked 1 Time in 1 Post
Default

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.



 
Old July 26th, 2005, 12:12 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
 
Old August 3rd, 2005, 08:34 AM
Registered User
 
Join Date: Jul 2005
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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)







Similar Threads
Thread Thread Starter Forum Replies Last Post
Word Mail Merge Automation using ASP.Net sbhatia ASP.NET 1.0 and 1.1 Professional 3 November 29th, 2013 07:18 AM
Access/Vba mail merge to word help alfonse Access VBA 0 July 28th, 2005 05:11 AM
Access to Word Mail Merge Automation kareltje Access VBA 6 June 9th, 2005 04:54 AM
External db files to Excel, via VBA automation ?? cipher_nb Excel VBA 1 December 13th, 2004 04:56 AM
Access automation william.murray BOOK: Access 2003 VBA Programmer's Reference 3 July 30th, 2004 07:59 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.