[Access] How to create tables in emails using VBA in access?

[spidermman]

Hi

I have a form in access which contains a 6 x 4 table and some instructions (text) on loading of products.

I would like to create an email based on the template of this form.

How do you create tables in emails using VBA in access?

[LEFT]DoCmd.SendObject[/LEFT]

thanks

 

[jwhite]

If you want the "table" of data to be in the body of the email, then you will need to output as HTML and use HTML formatting. I've attached a demo I made awhile back, and here is the thread the demo was prepared for.

 

[sunilvedula]

hi i am using lotus notes. when i tried your codes it says dataobject not found. i did some change like Dim olApp As Lotusnotes.Application but still the error persists. what i want is i have written a code where it runs a query and attaches the results but i want it formatted like in a table. can you help?

 

[Dennisk]

wht not output the data to a spreadsheet and attach that?

 

[sunilvedula]

that is how my management wants. THey are not interested in attaching but if possible to be able to send them in lotus with formatting

 

[jwhite]

I'm not familiar with Lotus Notes. The writing of the email body should be the same, while the rest is probably different. One/some/all of these links should help you:

• Lotus Notes appointment from VB or VBA
  Lotus Notes Appointments from VB or VBA (version 6)
  Lotus Notes Send EMail from VB or VBA
  Send Email: E-Mail via Lotus Notes or Outlook
  Send Email: Function to send email via Lotus Notes
  Send Email: Lotus Notes Email from Access

 

[sunilvedula]

Hi ,

Thank you for all the links. I thelps. But the main problem i am facing is unable to create rows and colums for the data that is being pasted on the body of the email in lotus. If any one can help pls???

 

[jwhite]

It would help if you state what 'problem' it is that you are having. What is the output you are getting?

If you followed the general outline of the sample module in the posted MDB, you shouldn't have had any problem. Your RecordSet would be the same that you would feed a report, and then you iterate through the returned records. strTableHeader defines the headers. The Do Until Loop iterates the records returned. Within that block you will see a TD for each within a TR /TR for each line item. This will work if you are formatting for HTML output. Note toward the end that the objMail.HTMLBody is being set to the formatted output. Does Lotus Notes support HTML? If so, is the property different from HTMLBody?

 

[sunilvedula]

i am getting the ouput as follows:
field 1 field2 field3 field4
abcd 1234 oollok 5412

i want the above result to have rows and coloums. That would help. i have no idea how to output in HTML. tried but failed. i am putting my code below. If you can help that would be great.

'***** to send email for JTMP status cases where no doc is received ******'
Dim rst As DAO.Recordset
Dim strEmailAddresses As String
Dim strccemail As String
Set rst = CurrentDb.OpenRecordset("qEmailstatus")
strccemail = ("Pradeep DAS/DPI/HDPI/HSBC@HSBC03" & "," & "Surita BHATTACHARJEE/DPI/HDPI/HSBC") & "," & ("Rizwan1 KHAN/HDPI/HSBC@HSBC03")
Do Until rst.EOF
strEmailAddresses = strEmailAddresses & rst("Email") & ","
rst.MoveNext
Loop
'Text in the email
Dim rst2 As DAO.Recordset
Dim strNames As String
strNames = "Please find attached details of the item for which doc is still not reflection on Master Comm"
Set rst2 = CurrentDb.OpenRecordset("qMailCheckJTMP")
Do Until rst2.EOF
strNames = strNames & vbCrLf & "AREA" & " " & " CARDNUMBER" & " " & "ORGDT" & " " & "RESPCODE" & " " & "ATM" & " " & "TIME" & " " & "STLMTAMT" & " " & "SWITCH"
strNames = strNames & vbCrLf & rst2("AREA") & ", " & rst2("Pan") & ", " & rst2("Orgdt") & ", " & rst2("Respcode") & ", " & rst2("ATM") & ", " & rst2("Time") & ", " & rst2("STLMTAMT") & ", " & rst2("Switch")
rst2.MoveNext
Loop
' send email
DoCmd.SendObject acSendQuery, "qMailCheckJTMP", , strEmailAddresses, strccemail, , "No Doc Received Report", strNames, False
strEmailAddresses = ""

Also if the system is configured to outlook then it is going to outlook even if lotus is kept open. HOw do i counter this problem since i have to use lotus only.

 

[jwhite]

The code you posted has not followed the example in any shape or form. You should be using the example, and then update the Email application properties appropriately for Lotus Notes (refer to the references I gave). I do not know Lotus Notes, therefore I gave links, no directions!
With regard to the formation of the email body...

The Header line (Area, CardNumber, etc) goes in strTableHeader.

The Detail Lines go in strTableBody.

After "'Totals", take this section out if you don't have totals, or modify if you do.

The email addresses are assigned with .To = {emailaddresseshere}.

You will have to update the DIM statements as well as the statements after "'Create e-mail item" to conform to call Lotus Notes instead of Outlook.

 

[sunilvedula]

hi
what you said is right. i have written this code earlier. i did try using your code but for me .to etc... are not supported. I also am not aware if lotus supports .html or not? Finally i wrote a code similar to the one you supplied and it is working but body problem still persists. i am pasting my new code just for reference. This works fine for attachements but body i have omitted. (since i wasnt able to do it right).

Private Sub CmdAwaitingResponse_Click()
'*** checking for any new cases *****'
If DLookup("[Check]", "qCheckNewCase(Accquiring)") = 0 Or IsNull(DLookup("[Check]", "qCheckNewCase(Accquiring)")) Then
MsgBox " No New cases Available to send the exception report", vbCritical + vbDefaultButton1 + vbOKOnly, "Cases Unavailable for exception report"
Exit Sub
End If
MsgBox "Sending an email through lotus", vbCritical + vbDefaultButton1 + vbOKOnly, "SEND EMAIL"
'*** SAVING THE QUERY RESULT IN A FIXED LOCATION ******'
DoCmd.OutputTo acOutputQuery, "qException(ACCQUIRING)", acFormatXLS, "C:\ACCQUIRINGEXCEPTION.xls"
DoCmd.OutputTo acOutputQuery, "qException(ISSUING)", acFormatXLS, "C:\ISSUINGEXCEPTION.xls"
'**** NEW CODE TESTING AS OF MAY 26, 2009*******************************************************************************************************'

Dim rst As New ADODB.Recordset
Dim rst1 As New ADODB.Recordset
Dim NumRecords, CurrentRecord
Dim Str As String
Dim strquery As String
'Email Variables
Str = "select TblUserEmail.Email from TblUserEmail"
With rst
.CursorLocation = adUseServer
.Open Str, CurrentProject.Connection, 3, 3
.MoveFirst
NumRecords = .RecordCount
End With
Dim notesdb As Object
Dim notesdoc As Object
Dim notesrtf As Object
Dim notessession As Object
Set notessession = CreateObject("Notes.Notessession")
Set notesdb = notessession.getdatabase("", "")
Call notesdb.OPENMAIL
'Loop
Do While Not rst.EOF
CurrentRecord = rst!EMAIL
Set notesdoc = notesdb.createdocument
Call notesdoc.replaceitemvalue("Sendto", "" & CurrentRecord)
Call notesdoc.replaceitemvalue("Subject", "hi ")
Set notesrtf = notesdoc.createrichtextitem("BODY")
Call notesrtf.appendtext("TESTING FOR ATTACHMENTS")
Call notesrtf.addnewline(2)
Rem attach Error Report doc
's = "C:\qExcepton(ACCQUIRING).XLS" + "C:\qExcepton(ACCQUIRING).XLS" + "qExcepton(ACCQUIRING).XLS"
Call notesrtf.embedObject(1454, "", "C:\ACCQUIRINGEXCEPTION.xls", "qException(ACCQUIRING)")
Call notesrtf.embedObject(1454, "", "C:\ISSUINGEXCEPTION.xls", "qException(ISSUING)")
notesdoc.SaveMessageOnSend = True
Call notesdoc.Send(False)
Set notessession = Nothing
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Kill ("C:\ACCQUIRINGEXCEPTION.xls")
Kill ("C:\ISSUINGEXCEPTION.xls")
'*** changing the status of the accquiring items to RR status *****'
DoCmd.SetWarnings False
DoCmd.RunSQL "Update TblAccq set TblAccq.Status = 'RR' where TblAccq.Queue = 'ACCQUIRING' AND TblAccq.Status is null;" ' RR STATUS MEANS RESPONSE REQUESTED
DoCmd.SetWarnings True
End Sub

 

[stephengrenfell]

If you want the "table" of data to be in the body of the email, then you will need to output as HTML and use HTML formatting. I've attached a demo I made awhile back, and here is the thread the demo was prepared for.

Hi, I am interested in using your demo module. However when I type : Call ReportToOutlookBody <Enter> in the Immediate Window I get a Compile error. "User define type not defind." with the following row highlighted

Dim olApp As Outlook.Application

I am not familiar with Visual Basic. Do you have any ideas how to solve this ?.

many thanks

 

[darbid]

In the visual basic editor you need to add a reference to Outlook.

 

[surfdude300zx]

This code seems to break when there is not a value listed in the data. Is there a way to make the code continue if it comes accross a null value?

 

[Chandrajit]

jwhite simply superb!

 

[hemantparmar]

If you want the "table" of data to be in the body of the email, then you will need to output as HTML and use HTML formatting. I've attached a demo I made awhile back, and here is the thread the demo was prepared for.

Thank JWhite. This was useful to me. Please guide me how to control the width of the Columns? Right now the column widths are just enough to hold the content. Can we define the column widths? OR can we add some padding on the left and right, within the cell?

 

[The_Doc_Man]

hemantparmar, please note two things:

First, this thread was last updated (before you) in October of 2014 - 5 1/2 years ago.

Second, I looked for you just in case. jwhite hasn't posted here since 2016 and may have moved on, died, or lost interest in the forum.

I have no HTML experience so cannot help you with your technical question. However, from a practical forum viewpoint, you might do better to start a new thread and reference this one (by copying the link at the top of the page and pasting it in your new thread). You would get more attention tailored to your needs by starting a new thread.

 

[hemantparmar]

hemantparmar, please note two things:

First, this thread was last updated (before you) in October of 2014 - 5 1/2 years ago.

Second, I looked for you just in case. jwhite hasn't posted here since 2016 and may have moved on, died, or lost interest in the forum.

I have no HTML experience so cannot help you with your technical question. However, from a practical forum viewpoint, you might do better to start a new thread and reference this one (by copying the link at the top of the page and pasting it in your new thread). You would get more attention tailored to your needs by starting a new thread.

Thanks. I partly figured it out using some html.