X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> Ms Access Vba Generates A Table Within An Email Body Depends On An Email Address, Access 2010    
post Jun 14 2019, 08:38 PM

Posts: 115
Joined: 8-July 17

Hi Everyone,

I have got a VBA code that generates a table in an outlook email body from a query.

Query Name: qryFileReqEmailAck
Field one: [Account Number]
Field two: [Customer]
Field three: [EmailID]
Field four: [UserName]

I would like to redefine the code to get the result like the following as a table:

1. User 1 will get an email with the following two fields as a table result:
A/C No. Customer's Name
6600-100000-300 MTC VODAFONE
6600-102401-001 ABC

1. User 2 will get an email with the following two fields as a table result:
A/C No. Customer's Name
6600-100025-300 ZEX
6600-102466-001 KLM

(A/C No.) represents the result of [Account Number]
(Customer's Name) represents the result of [Customer]

The rest fields will segregate the email body depends on the UserName in its EmailID address.

Below is my VBA code:
Private Sub cmdOpenEmail_Click()
On Error GoTo ErrorHandler

Dim strMsg As String
Dim iResponse As Integer

  Me.Dirty = False

'''''''''''''''''' tools -> refrence -> Microsoft outlook
Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim mailbody As String
Dim rs As DAO.Recordset

'********************* created header of table
   mailbody = "<TABLE Border=""1"", Cellspacing=""0""><TR>" & _
   "<TD Bgcolor=""#2B1B17"", Align=""Left""><Font Color=#FCDFFF><b><p style=""font-size:18px"">A/C #&nbsp;</p></Font></TD>" & _
   "<TD Bgcolor=""#2B1B17"", Align=""Left""><Font Color=#FCDFFF><b><p style=""font-size:18px"">Customer's Full Name&nbsp;</p></Font></TD>" & _

Set rs = CurrentDb.OpenRecordset("qryFileReqEmailAck", dbOpenDynaset)
Do While Not rs.EOF

              mailbody = mailbody & "<TR>" & _
               "<TD ><Left>" & rs.Fields![Account Number].Value & "</TD>" & _
               "<TD><left>" & rs.Fields![Customer].Value & "</TD>" & _


' <br> used to insert a line ( press enter)
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
With olMail
.To = " & [EmailID] & "
.CC = ""
.Subject = "Retrieval Request"
.HTMLBody = "** This is a system-generated email message ** <br><br>" & _
"Dear " & [UserName] & ", <br><br> Kindly arrange to provide me with the physical <mark> " & _
</mark> as per the below details: <br><br> " & mailbody & _
"</Table><br> <br>Your assistance in this matter would be highly appreciated. " & _
"<br> <br>Regards,<br>" & [Forms]![NavigationForm]![txtUserName] & ""
End With

    'Release all of the object variables
    Set olApp = Nothing
    Set olMail = Nothing

  Exit Sub

  Select Case Err.Number
    Case 2501
      MsgBox "Email message was Cancelled."
    Case Else
      MsgBox Err.Number & ": " & Err.Description
  End Select
  Resume Cleanup

End Sub

Thanks a lot in advance!

Best Regards,

Al Hakeem1977
Go to the top of the page

Posts in this topic

Custom Search

RSSSearch   Top   Lo-Fi    21st August 2019 - 07:23 PM