Send automatic e-mail

**Csalge** · 04-08-2013, 09:03 AM

Hi,

I have a form with a command button that when pressed it's supposed to e-mail all individuals based on a query result. Here's my code:
Private Sub Command92_Click()

Dim mydb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim stoName As String
Dim sSubject As String
Dim smessagebody As String
Set mydb = CurrentDb()
Set rsEmail = mydb.OpenRecordset("qryexcludednoemail", dbOpenSnapshot)
With rsEmail
.MoveFirst
Do Until rsEmail.EOF
If IsNull(.Fields(0)) = False Then
stoName = .Fields(0)
sSubject = "Excluded time requested for: " & .Fields(2)
smessagebody = "The following excluded time requested: " & vbCrLf & _
"Type Requested: " & .Fields(3) & vbCrLf & _
"Notes with Excluded Time Requested: " & .Fields(4) & vbCrLf & _
"Reason for Denial: " & .Fields(5)

DoCmd.SendObject acSendNoObject, , , stoName, , , sSubject, smessagebody, False, False

End If

.MoveNext
Loop
End With

Set mydb = Nothing
Set rsEmail = Nothing

It works but I have two problems. For my message body I would like for each field to be on a separate line in the body of my email. I'm not sure how to do that. Right now all the text is all together. My second problem is that one of the fields in my query is based on a combo box and my e-mail message shows the ID field instead of the text field associated with it (as displayed in my query).

**June7** · 04-08-2013, 11:33 AM

If the vbCrLf is ignored then probably need to use Outlook object VBA code. Example:

Code:

Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
    .BodyFormat = olFormatRichText
    .To = "email address here"
    ''.cc = ""
    ''.bcc = ""
    .Subject = "subject here"
    .HTMLBody = "<HTML><BODY>some text here <br>more text here</body></html>"
    '.Attachments.add ()
    .DeleteAfterSubmit = True 'to not save in sent bin
    ''.Display
    .Send
End With

The olFormatRichText might be enough to cause recognition of vbCrLf. If not, then need to use HTML formatted body with HTML code tags. Or maybe both are needed, can't remember why I have both lines in code.

Is the field in the query the field from table that is storing the ID and it has a lookup for displaying the text? This is one reason not to build Lookups in tables, can't see the real value and is misleading. Need to retrieve the text field by joining tables.

Send automatic e-mail

Thread Tools

Send automatic e-mail

Similar Threads

Send Report by mail for the mail Ids present in the table from a button click

Send multiple mail

Send E-Mail 2010

send a form via mail

Send mail to the chosen ones

Posting Permissions