Alhakeem1977
Registered User.
- Local time
- Today, 17:00
- Joined
- Jun 24, 2017
- Messages
- 308
Hi,
Trying to solve this issue with no success, to generate an email body with a table.
Below is my vba code and the query below the code:
When I remove the parameter from the query it works fine but with parameter, it dosesn't Error 3061
Thanks a lot in acvance!
Trying to solve this issue with no success, to generate an email body with a table.
Below is my vba code and the query below the code:
When I remove the parameter from the query it works fine but with parameter, it dosesn't Error 3061
Code:
Private Sub cmdOpenEmail_Click()
On Error GoTo ErrorHandler
Dim strMsg As String
Dim iResponse As Integer
DoCmd.Beep
strMsg = "Are you sure you want to proceed?" & Chr(10)
strMsg = strMsg & ""
iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Notice")
If iResponse = vbNo Then
Me.Undo
DoCmd.Close acForm, "frmFileReqA", acSaveNo
Else
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
' <br> used to insert a line ( press enter)
' create a table using html
' check the link below to know more about html tables
' http://www.w3schools.com/html/html_tables.asp
' html color code
'http://www.computerhope.com/htmcolor.htm or http://html-color-codes.info/
'bg color is used for background color
' font color is used for font color
'<b> bold the text http://www.w3schools.com/html/html_formatting.asp
' is used to give a single space between text
'<p style="font-size:15px">This is some text!</p> used to reduce for font size
'********************* created header of table
mailbody = "<TABLE Border=""1"", Cellspacing=""0""><TR>" & _
"<TD Bgcolor=""#2B1B17"", Align=""Center""><Font Color=#FCDFFF><b><p style=""font-size:18px"">A/C No. </p></Font></TD>" & _
"<TD Bgcolor=""#2B1B17"", Align=""Center""><Font Color=#FCDFFF><b><p style=""font-size:18px"">Customer's Name </p></Font></TD>" & _
"</TR>"
Set rs = CurrentDb.OpenRecordset("qryFileReqEmail", dbOpenDynaset)
rs.MoveFirst
Do While Not rs.EOF
mailbody = mailbody & "<TR>" & _
"<TD ><center>" & rs.Fields![Account Number].Value & "</TD>" & _
"<TD><center>" & 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 = "#########@###.com"
.Cc = ""
.Subject = "Account File/s Physical Retrieval Request"
.HTMLBody = "Dear DMS <br><br> ""Kindly arrange to provide me with the physical Account file/s as per the below details: <br><br> " & mailbody & _
"</Table><br> <br>Your assistance in this matter would be highly appreciated.<br> <br>Regards, <br> <br> " & [Forms]![NavigationForm]![txtUserName] & ""
.Display
'.Send
MsgBox "Your email has been generated successfully!"
End With
DoCmd.Close acForm, "frmFileReqA", acSaveNo
End If
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
Code:
SELECT Customerstbl.FID, [Branch] & "-" & [AccountNumber] AS [Account Number], Customerstbl.Customer, [Userlogin] & " - " & [UserName] AS [Requested by], tblFileReq.ReqPk, tblFileReq.IsActive
FROM tblUser INNER JOIN (Customerstbl INNER JOIN tblFileReq ON Customerstbl.FID = tblFileReq.[AccountNo]) ON tblUser.UserID = tblFileReq.ReqID
WHERE (((tblFileReq.IsActive)=True) AND ((tblFileReq.ReqID)=[Forms]![NavigationForm]![txtID]))
ORDER BY [Branch] & "-" & [AccountNumber], Customerstbl.AccountNumber;
Thanks a lot in acvance!