UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Ms Access Vba Generates A Table Within An Email Body Depends On An Email Address, Access 2010    
 
   
Alhakeem1977
post Jun 14 2019, 08:38 PM
Post#1



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:
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>" & _
      "</TR>"
      

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

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

rs.MoveNext
Loop
rs.Close

' <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] & ""
.Display
''.Send
End With

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

Cleanup:
  Exit Sub

ErrorHandler:
  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
 
MadPiet
post Jun 14 2019, 09:08 PM
Post#2



Posts: 3,206
Joined: 27-February 09



I think I understand where you're going with this.

Basically, you're looping through a list of customers and their invoices(?) and e-mailing them the result of the "invoices" query (I might be wrong, but hopefully it's close). Here's how I would do it:

1. Write a query to get the CustomerID, EMail address etc (for the "header" of the e-mail). Looks like you've done that. then you can create an e-mail message and assign the Recipient e-mail and subject etc. from that recordset.

2. Write another query (a parameter query) to get the "Invoices" that belong to that customer. Use a function to return the HTML table of records. Then you'd have something along the lines of this in your function that returns the invoices HTML table.

Function fInvoicesTable (ByVal CustomerID As Long) As String
dim qdf As DAO.Querydef
dim strHTML as string
set qdf = CurrentDb.QueryDefs("qprmInvoicesTableParameterQuery")
qdf.Parameters("prmCustomerID") = CustomerID
set rsInvoices = qdf.OpenRecordset
' build your invoice table here as you did before
do until rsInvoices.EOF

strHTML = strHTML & "<tr>" etc etc etc
rsInvoices.MoveNext
loop

' add any other bits you need to to the string to return the table then... return the value of strHTML to InvoicesTable function
fInvoicesTable = strHTML

' close and destroy all your referenced objects
...
End function

So then your outer routine grabs data from the first "outer" recordset ( the customers ). then you set the body of the e-mail equal to the fInvoicesTable(lngCustomerID) and you're set, right?
Go to the top of the page
 
Alhakeem1977
post Jun 15 2019, 08:51 AM
Post#3



Posts: 115
Joined: 8-July 17



I am got an error I can not figure to reference two fields:

1. [EmailID]
2. [UserName]

Error: 3420: Object invalid or no longer set, I highlighted them in red

Here is my VBA code:

CODE
Private Sub cmdDispatch_Click()
Beep
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>" & _
      "</TR>"
      

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

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

' <br> used to insert a line ( press enter)
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
With olMail

'To & "<TR>" & rs.Fields![EMailID].Value & "</TR>"     '" & rs.Fields![EMailID] & "    " & rs.Fields![UserName] & ",

[color="#FF0000"].To = " & rs.Fields![EMailID].Value & "[/color]
.CC = ""
.Subject = "Response on File Retrieval Request"
.HTMLBody = "** This is a system-generated email message ** <br><br>" & _
"Dear" [color="#FF0000"]& rs.Fields![UserName] & "[/color], <br><br> This is to inform you that your file/s retrieval request is/are ready to be dispatched <mark> " & _
"</mark> as per the below details: <br><br> " & mailbody & _
"</Table><br> <br>Appreciate acknowledge it/them upon receipt. " & _
"<br> <br>Regards,<br>VVV@ABC.com"
.Display
'.Send
' <br> used to insert a line ( press enter) and send email
End With


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

Cleanup:
  Exit Sub

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

--------------------
Best Regards,

Al Hakeem1977
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th August 2019 - 03:43 PM