Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    MSAccessOldTimer is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    May 2012
    Posts
    46

    Thumbs up Add Query Field Data to an HTML Email Module

    Hopefully I can explain without overcomplicating what I am trying to do.



    I have 2 reports, A & B. Both of these are generated off a unique number selected from a combo box on a report form. There are 2 components to this process. First, once the user selects the unique number for the reports they want to generate, and then press a "Generate Report" button, these 2 reports are autogenerated and then exported to a temporary holding spot on our company network. This happens without the user even seeing the reports. Once that is done, a module runs, that auto-inserts both reports, as well as a WORD document, into an email. Additionally, the subject line and body of that message are auto-filled based on the text defined in the module. I should point out that this generates an HTML formatted email. All works perfectly (pretty proud of myself actually), and by using HTML, I am able to customize the font where needed, etc. to highlight key information. The end user see the email, adds the recipients names, can modify the text (if needed) and then when ready, sends it out just like any other email.

    Except...there is one area in the text where they have to add a date. These reports are based on auditing activity, and in the email text, it states "...audit completed on *INSERT DATE*". The user replaces the *insert Date* text with the actual date the audit was completed.

    So naturally I had an end user ask me if there is a way to actually include the date of the audit in the text portion, versus having to manually type it in. Yes, I know...give them an inch, they want a mile...but being a semi-geek, I too want to know if it is possible, not only for this project, but in case I want to add date fields in other email modules. The [audit date] is included in the same queries that feed the reports, so I would only need to pull or reference one of those. The other caveat is the date must be tied to the unique number tied to the combo box.

    Any ideas or suggestions are greatly appreciated.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Seeing the code would help to suggest where to integrate this. You can get a value from a report field if you are opening it (note, OutPutTo by itself won't work) but this should be in the report header. Any field that repeats means there would be multiple instances of it, thus getting a particular value would be a problem. You could also create a recordset based on the report query. You could also do DLookup - probably many ways. So if you're looking for more direction, code and where the date in the report is and where it comes from would be a start.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Should be pretty simple. But without seeing your code it's hard to tell you specifically how you need to do it in your code.

    In your vba that your using to create the email simply dlookup the date and stick it in the string.

    Dim mydate as DateTim
    Dim emailbody as String
    mydate = dlookup("datefield", "table", "Id=" & [unique number from report form])

    Emailbody = "...audit completed on " & format(mydate, "mm/dd/yyyy")

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    "...audit completed on " & Format(dlookup("[AuditDate]","[qrySourceOfReport]","[ID_NUMBER]= " & Forms!frmYourForm!Unique_Number),"MMM DD,YYYY") & "."

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    MSAccessOldTimer is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    May 2012
    Posts
    46
    Here is the code from the module. The area were the date would be inserted is shown in blue font.

    Gicu, thank you for your reply. I will give your response a try on my code.


    Public Sub EmailAuditFindings()


    Dim myMail As Outlook.MailItem
    Dim myOutlApp As Outlook.Application
    Dim att As Outlook.Attachment


    ' Creating an Outlook-Instance and a new Mailitem
    Set myOutlApp = New Outlook.Application
    Set myMail = myOutlApp.CreateItem(olMailItem)


    With myMail
    'setting the recipient
    .To = ""

    'defining a subject for the mail
    .Subject = " Internal Audit Report(s) Attached"

    'Setting BodyFormat and body-text of the mail
    .BodyFormat = olFormatHTML
    .HTMLBody = _
    "<BODY><HTML>The attached report(s) are from the ISO internal audit(s) completed in your area of responsibility on *INSERT DATE*<br><br><b>NONCONFORMANCE REPORT(s):</b><br>The Nonconformance Report attached to this email, may contain more than one report. And if more than one management system was audited, then each report will identify which ISO management system the nonconformance applies to. Per the QP9-001 Management System Audit Procedure, (a) due date(s) for the attached nonconformance report(s) must be communicated to me within 3 working days from this notification or I will be required to set (a) due date(s) for you." _
    & vbCrLf & "The attached QF9-008 Internal Audit Cause-Countermeasure Report must be completed for each Nonconformance Report. All areas of this form must be completed effectively, or it will be returned. This could jeopardize meeting the defined due date.<br><br>Once the completed Nonconformance and related QF9-008 Internal Audit Cause-Countermeasure Report(s) are returned to the ISO Section a follow verification audit must be completed before the report(s) can be closed. If the nonconformance is NOT corrected by the defined Due Date, the OVERDUE Escalation process will be implemented.<br><br>" _
    & vbCrLf & "<b>SUMMARY REPORT(s):</b><br>The Summary Report attached to this email, may contain observations, as well as other findings. And if more than one management system was audited, each finding will identify which ISO management system it applies to. PLEASE NOTE: The items identified in the OBSERVATIONS area of the report do NOT require written corrective action be submitted to the ISO Section, but they will be subject to being checked during future internal audits. As is noted on the report, if similar findings exist at subsequent audits, then they could be escalated to a Nonconformance.<br><br>" _
    & vbCrLf & "Thank you again for your cooperation during the internal audit(s). If you have any questions about these reports, or the findings, do not hesitate to contact me.</BODY></HTML>"



    'Adding an attachment from filesystem
    Set att = .Attachments.Add("\\siapdc\vol2\ISO\Library\Databa se\AuditReports\rptNonconformanceReport_ByAuditNum .pdf")
    Set att = .Attachments.Add("\\siapdc\vol2\ISO\Library\Databa se\AuditReports\rptSummaryReport_ByAuditNum.pdf")
    Set att = .Attachments.Add("\\siapdc\vol2\ISO\Section ISO\9001 QMS Library\2 - Forms\QF9-008 Internal Audit Cause-Countermeasure Report.docx")


    'View the mail
    .Display

    End With


    End Sub

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    Use a simple Replace() ?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    MSAccessOldTimer is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    May 2012
    Posts
    46
    Close...but I am not doing something right. Here is that part of the code, and when it runs, it does everything correctly, and generate the email, but leave the audit date blank (in the email text). Also, when I put the code in the module, it turns red and throws an error on the last part, "." If remove the last ", the error goes away, but then again, no audit date. Unless you see something obvious that I may have missed, I will continue to try different variations of the code. Thanks!

    ...completed in your area of responsibility on " & Format(DLookup("[AuditDate]", "[qrySummaryReport_ByAuditNum]", "[ExtAudit]= " & Forms!ReportMenu!cmbAuditNo), "MMM DD,YYYY") & ".”

  8. #8
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    1. Put your criteria into a string then you can use it in the DLookup after you have Debug.Print it.?
    2. Try a Replace after you successfully get the date.?

    Basically ensure the DlookUp() works.
    You can even issue it in the immediate window to see what if anything is returned.?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    At least the last quote is one of those characters that Access doesn't usually play nice with ( ” ). We see that all the time here. But Welshgasman's suggestions are on point.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Do you have it like this?
    Code:
    .HTMLBody = _
    "<BODY><HTML>The attached report(s) are from the ISO internal audit(s) completed in your area of responsibility on " & Format(DLookup("[AuditDate]", "[qrySummaryReport_ByAuditNum]", "[ExtAudit]= " & Forms!ReportMenu!cmbAuditNo), "MMM DD,YYYY") & ".<br><br><b>NONCONFORMANCE REPORT(s):</b><br>The Nonconformance Report attached to this email, may contain more than one report. And if more than one management system was audited, then each report will identify which ISO management system the nonconformance applies to. Per the QP9-001 Management System Audit Procedure, (a) due date(s) for the attached nonconformance report(s) must be communicated to me within 3 working days from this notification or I will be required to set (a) due date(s) for you." _
    & vbCrLf & "The attached QF9-008 Internal Audit Cause-Countermeasure Report must be completed for each Nonconformance Report. All areas of this form must be completed effectively, or it will be returned. This could jeopardize meeting the defined due date.<br><br>Once the completed Nonconformance and related QF9-008 Internal Audit Cause-Countermeasure Report(s) are returned to the ISO Section a follow verification audit must be completed before the report(s) can be closed. If the nonconformance is NOT corrected by the defined Due Date, the OVERDUE Escalation process will be implemented.<br><br>" _
    & vbCrLf & "<b>SUMMARY REPORT(s):</b><br>The Summary Report attached to this email, may contain observations, as well as other findings. And if more than one management system was audited, each finding will identify which ISO management system it applies to. PLEASE NOTE: The items identified in the OBSERVATIONS area of the report do NOT require written corrective action be submitted to the ISO Section, but they will be subject to being checked during future internal audits. As is noted on the report, if similar findings exist at subsequent audits, then they could be escalated to a Nonconformance.<br><br>" _
    & vbCrLf & "Thank you again for your cooperation during the internal audit(s). If you have any questions about these reports, or the findings, do not hesitate to contact me.</BODY></HTML>"
    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  11. #11
    MSAccessOldTimer is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    May 2012
    Posts
    46
    Gicu, yes that is correct....and the result in the email looks like this: "...audit(s) completed in your area of responsibility on ." The period at the end is also part of the email text.

  12. #12
    MSAccessOldTimer is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    May 2012
    Posts
    46
    Welshgasman - thinking about your suggestion, I am not clear on what you are asking me to do.

  13. #13
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    Quote Originally Posted by MSAccessOldTimer View Post
    Welshgasman - thinking about your suggestion, I am not clear on what you are asking me to do.
    Well I would not have the * in the placeholder, but try it anyway.
    I am suggesting just replace the placeholder with the Format() of your date? That is what placeholders are for, are they not?

    Code:
    Dim dtAuditDate as Date
    
    dtAuditDate = DLookup("[AuditDate]", "[qrySummaryReport_ByAuditNum]", "[ExtAudit]= " & Forms!ReportMenu!cmbAuditNo)
    Debug.Print "Audit date is " & dtAuditDate
    .html = Replace(.html,"*INSERT DATE*",Format(dtAuditDate,""MMM DD,YYYY"))
    Post back what the debug.print produces.
    Last edited by Welshgasman; 02-10-2021 at 09:03 AM. Reason: Added post back request
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  14. #14
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Looks like the dLookup doesn't return the date, are you sure the ExtAudit is numeric? If it's text you need to wrap it in single quotes.
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  15. #15
    MSAccessOldTimer is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    May 2012
    Posts
    46
    Thank you Welshgasman. I am working on another issue at the moment, but will try your code later today. I will let you know how it goes.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. HTML formatting me.field in VBA email
    By jjake88 in forum Programming
    Replies: 1
    Last Post: 02-08-2019, 10:30 AM
  2. Send email as HTML template
    By fletcjas in forum Programming
    Replies: 5
    Last Post: 12-03-2018, 08:31 AM
  3. SendObject Email HTML Text
    By abodi in forum Programming
    Replies: 1
    Last Post: 09-10-2014, 12:03 AM
  4. Email HTML using Access
    By Ruegen in forum Access
    Replies: 2
    Last Post: 08-24-2014, 11:52 PM
  5. New line in html email form field
    By srk999 in forum Forms
    Replies: 3
    Last Post: 02-18-2014, 06:34 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums