Page 2 of 2 FirstFirst 12
Results 16 to 23 of 23
  1. #16
    MSAccessOldTimer is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    May 2012
    Posts
    46
    Gicu- the ExtAudit field is NOT numberic....it is a field from a query that combines a text and a numeric field. There is a front part (text) "IAR" and the last part (numeric), auto-number. Ex. IAR-022

  2. #17
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    So that's your problem, try this:

    Code:
    Dim dtAuditDate as Date
    
    
    dtAuditDate = DLookup("[AuditDate]", "[qrySummaryReport_ByAuditNum]", "[ExtAudit]= '" & Forms!ReportMenu!cmbAuditNo & "'")
    You can use it directly in the body of the message or with Welshgasman's Replace().
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #18
    MSAccessOldTimer is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    May 2012
    Posts
    46
    I tried it, but as soon as I inserted it into the module, that portion of the overall code turned red. It is showing a compile error message.

  4. #19
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Can you try 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>"
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #20
    MSAccessOldTimer is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    May 2012
    Posts
    46
    It is getting hung up on the comma in front of "MMM DD, YYYY.

    !cmbAuditNo) & "'"), "MMM DD,YYYY") &

  6. #21
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Try to isolate the date in a string variable then use that in the body:

    Code:
    Dim dtAuditDate as Date,strAuditDate as String
    dtAuditDate = DLookup("[AuditDate]", "[qrySummaryReport_ByAuditNum]", "[ExtAudit]= '" & Forms!ReportMenu!cmbAuditNo & "'")
    strAuditDate =Format(dtAuditDate,"MMM DD, YYYY")
    .HTMLBody = _
    "<BODY><HTML>The attached report(s) are from the ISO internal audit(s) completed in your area of responsibility on " & strAuditDate & ".<br><br><b>NONCONFORMANCE......"
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #22
    MSAccessOldTimer is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    May 2012
    Posts
    46
    YES SIR!!!! That did it!!

    Thank you so much Gicu!

  8. #23
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Great news!
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Page 2 of 2 FirstFirst 12
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