Sending individual Access Reports as HTML body in email to multiple recipients (1 Viewer)

JeffBarker

Registered User.
Local time
Today, 00:26
Joined
Dec 7, 2010
Messages
130
Hi guys,

I've been looking at this for the last couple of days, and I've got as far as I can with it - so I'm on the lookout for some assistance, if anyone here is able to do that!

I am very much a layman when it comes to Access terminology, so I won't get offended if you guys explain things using smaller words than usual! :) So, here we go:

I have a subform sitting on an unbound form in our Membership Database, that lists all contacts whose memberships are about to expire - what I need to be able to do is to pull a report from the database into the HTML body of an Outlook email, creating one each for every contact in the subform.

I've tried multiple ways of doing this, and plumped with the idea of using a maketable query to create the base for the email data, with a recordset to read the data and then finally insert it into the HTML version of the report stored in the database.

So, the code I have creates an individual email for the two test records I currently have, but I can't work out how to tailor the HTML body text - I need the text to open with "Dear Ahmed" or "Dear Alan" etc.

Also, to compound this, the report contains a subreport which should only appear if the invoice number for the membership relates to more than one person - so, Mr Smith could be on the same membership as Mr Browne, or Mr Smith could have his own membership.

We do have membership types (Group, Individual, Honorary etc), so perhaps we could use these as a trigger for the Subreport to appear.

Finally - but no less important - when Outlook creates the email it's completely ignoring the commas I've entered into the textboxes in the report, so instead of the email reading "....updated, please..." it reads "...updatedplease...".

Here's my code:

Code:
Private Sub cboReportPicker_AfterUpdate()
On Error GoTo cboReportPicker_AfterUpdate_Err

    Dim db As DAO.Database
    Dim rsE1 As DAO.Recordset
    Dim report, EmailTo, EmailSubject As String

    Set db = CurrentDb
    Set rsE1 = db.OpenRecordset("tblRenewalEmail1", DB_OPEN_DYNASET)
    
report = Me.cboReportPicker

    Select Case report
        Case "60 Days Email"
        Dim MyItem, OL, strHTML, strline As String

        If Not rsE1.EOF And Not rsE1.BOF Then
        rsE1.MoveFirst
        

        Do Until rsE1.EOF
            Set OL = outlook.Application
            Set MyItem = outlook.Application.CreateItem(olMailItem)
                        
            DoCmd.OutputTo acOutputReport, "rptRenewalEmail1", acFormatHTML, ("C:\Membership Database\60 Day Renewals"), , ("C:\Membership Database\60 Day Renewals")
            
            Open ("C:\Membership Database\60 Day Renewals") For Input As 1
            Do While Not EOF(1)
                Input #1, strline
                strHTML = strHTML & strline
            Loop
            Close 1
            
            If Left(OL.Version, 2) = "10" Then
                MyItem.BodyFormat = olFormatHTML
            End If
            
            MyItem.HTMLBody = strHTML
            MyItem.To = rsE1!EMailTo
            MyItem.Subject = "Membership Renewal for - " & rsE1!Contact
            MyItem.Display

            rsE1.MoveNext
            Loop
        Else
            MsgBox "There are no Renewals to send via Email", vbOKOnly, "Membership Renewals"
        End If
        
        rsE1.Close

So, if any of you lovely people out there are able to amend the code or give me a (layman's) brief on how to sort this problem out, I would be most grateful.

Thanks in advance,

Jeff.
 

AccessMSSQL

Seasoned Programmer-ette
Local time
Yesterday, 17:26
Joined
May 3, 2012
Messages
636
You can add this to your code to add the dear person:
Code:
            strHTML = "Dear " & rsE1!FirstName & ":<br><br>" & strHTML
            MyItem.HTMLBody = strHTML
            MyItem.To = rsE1!EMailTo
 

AccessMSSQL

Seasoned Programmer-ette
Local time
Yesterday, 17:26
Joined
May 3, 2012
Messages
636
For the commas not appearing issue you can try replacing the commas in your table with it's corresponding ascii code 44. You would have to modify your report query:
something like this:

SELECT Replace(YourField1,",",chr(44)), Replace(YourField2,",",chr(44)) FROM tblRenewalemail1.

I'm not sure if this will work but you can try.
 

AccessMSSQL

Seasoned Programmer-ette
Local time
Yesterday, 17:26
Joined
May 3, 2012
Messages
636
Sorry, just tested this - it's an outlook issue. The html file appears okay with the commas but in Outlook they disappear. Let me see how to handle this one.
 

AccessMSSQL

Seasoned Programmer-ette
Local time
Yesterday, 17:26
Joined
May 3, 2012
Messages
636
Okay, so I found where the breakdown is occuring in your commas missing. It's happening when you are parsing the file. Your Do While Not EOF(1) Loop is interpreting it as a comma delimited file so it detects the commas as EOL marker.
 

AccessMSSQL

Seasoned Programmer-ette
Local time
Yesterday, 17:26
Joined
May 3, 2012
Messages
636
Here's an alternative way to loop through your file - this will maintain the commas:
Code:
   Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
            Set fs = CreateObject("Scripting.FileSystemObject")
            Set f = fs.GetFile("c:\database\test.html")
            Set ts = f.OpenAsTextStream(1, TristateUseDefault)
            Do While ts.readLine <> ""
            strHTML = strHTML & ts.readLine
            Loop
 
       [COLOR=yellowgreen]    ' Do While Not EOF(1)[/COLOR]
[COLOR=yellowgreen]           '     Input #1, strline[/COLOR]
[COLOR=yellowgreen]           '     strHTML = strHTML & strline[/COLOR]
[COLOR=yellowgreen]           ' Loop[/COLOR]
[COLOR=yellowgreen]           ' Close 1[/COLOR]
 

JeffBarker

Registered User.
Local time
Today, 00:26
Joined
Dec 7, 2010
Messages
130
Hey,

Thanks for replying - apologies for not getting on here sooner, I've been out of the office for a couple of days...I'm going to check these solutions out and then get back to you to let you know how I get on!! ;)
 

JeffBarker

Registered User.
Local time
Today, 00:26
Joined
Dec 7, 2010
Messages
130
Here's an alternative way to loop through your file - this will maintain the commas:
Code:
   Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
            Set fs = CreateObject("Scripting.FileSystemObject")
            Set f = fs.GetFile("c:\database\test.html")
            Set ts = f.OpenAsTextStream(1, TristateUseDefault)
            Do While ts.readLine <> ""
            strHTML = strHTML & ts.readLine
            Loop
 
       [COLOR=yellowgreen]    ' Do While Not EOF(1)[/COLOR]
[COLOR=yellowgreen]           '     Input #1, strline[/COLOR]
[COLOR=yellowgreen]           '     strHTML = strHTML & strline[/COLOR]
[COLOR=yellowgreen]           ' Loop[/COLOR]
[COLOR=yellowgreen]           ' Close 1[/COLOR]

Hey, thanks again for this - and apologies for taking ages to reply, I've been super busy and this project has kinda sunk to the bottom of the pile for a bit!!

I've implemented this code, but I just need to know what to set fs, f and TS as (string, integer, boolean), please?

Thanks! :)
 

JeffBarker

Registered User.
Local time
Today, 00:26
Joined
Dec 7, 2010
Messages
130
So, further to the above, I've had a bit of a play around and after a lot of googling I found that just 'dimming' FS, F and TS without specifying a type works - as in, it maintains the commas from my original report, but the text is all over the place...if someone can tell me how to add an attachment to this post I'll stick up a screen print image showing how it currently looks.

:banghead::banghead::banghead::banghead::banghead:
 

AccessMSSQL

Seasoned Programmer-ette
Local time
Yesterday, 17:26
Joined
May 3, 2012
Messages
636
Sorry for delay Jeff...I haven't been on the forums in a while due to other projects.

Dim fs As New FileSystemObject
Dim f As File
Dim ts as TextStream

As for your other question, go to the "Go Advanced" button. In that screen you should be able to add an attachment.
 

JeffBarker

Registered User.
Local time
Today, 00:26
Joined
Dec 7, 2010
Messages
130
Sorry for delay Jeff...I haven't been on the forums in a while due to other projects.

Dim fs As New FileSystemObject
Dim f As File
Dim ts as TextStream

As for your other question, go to the "Go Advanced" button. In that screen you should be able to add an attachment.

Hey, absoloutely no problems at all - thanks for taking the time to get back to me.

So I've set these parameters here:

Code:
    Select Case Report
        Case "60 Days Email"
        Dim MyItem, OL, strHTML, strLine As String
        Dim fs As New FileSystemObject
        Dim f As File
        Dim ts As TextStream

But when I run the code I get a 'Compile Error: User-defined type not defined' pointing to Dim fs As New FileSystemObject.

Also, when I Go Advanced on a post, I can add an Image attachment, but it asks for a URL - can we not upload straight from our desktops?

With regard to this:

Code:
            strHTML = "Dear " & rsE1!FirstName & ":<br><br>" & strHTML
            MyItem.HTMLBody = strHTML
            MyItem.To = rsE1!EMailTo

It adds the 'Dear John' name fine, but in a completely different font to the rest of the email body text...is there a way of changing this code so that it matches, please?
 

AccessMSSQL

Seasoned Programmer-ette
Local time
Yesterday, 17:26
Joined
May 3, 2012
Messages
636
You should declare all variable types in your Dim statements.

Here:
Code:
 Case "60 Days Email"
        Dim MyItem as Outlook.MailItem
        Dim OL as Outlook.Application
        Dim  strHTML as string
        Dim  strLine As String
        Dim fs As New FileSystemObject
        Dim f As File
        Dim ts As TextStream

To use FileSystemObject you need to add a Reference to the Microsoft Scripting object library. Go into any code window and go to Tools > References and checkmark the library for this. It should solve the user-defined type not defined. I have attached a screenshot.

To solve the font issue try changing the <br><br> to <br/><br/>. Not sure if this will work but you can try it. Also, check the font type that is set in your actual report for the body text. This also might be the issue.
 

Attachments

  • MicrosoftScriptingRuntime.jpg
    MicrosoftScriptingRuntime.jpg
    43.9 KB · Views: 454

AccessMSSQL

Seasoned Programmer-ette
Local time
Yesterday, 17:26
Joined
May 3, 2012
Messages
636
Also, just a matter of coding practice, you should declare all of your variables at the top of your procedure, not within a protected code block (like a case statement). It should look something more like this:

Code:
Private Sub cboReportPicker_AfterUpdate()
On Error GoTo cboReportPicker_AfterUpdate_Err

    Dim db As DAO.Database
    Dim rsE1 As DAO.Recordset
    Dim report as DAO.Report
    Dim EmailTo as String
   Dim EmailSubject As String
   Dim MyItem as Outlook.MailItem
        Dim OL as Outlook.Application
        Dim  strHTML as string
        Dim  strLine As String
        Dim fs As New FileSystemObject
        Dim f As File
        Dim ts As TextStream

    Set db = CurrentDb
    Set rsE1 = db.OpenRecordset("tblRenewalEmail1", DB_OPEN_DYNASET)
    
report = Me.cboReportPicker

    Select Case report
        Case "60 Days Email"
 

JeffBarker

Registered User.
Local time
Today, 00:26
Joined
Dec 7, 2010
Messages
130
Hello! Thanks for this advice!

I've amended the code accordingly, and I will certainly take on board good practice of declaring all variables in the first block of code in the future.

I've set a reference to Microsoft Scripting Runtime, and that error no longer appears! :)

Unfortunately, the font issue still exists, but I think I've found a cheeky way around that by using the Replace function:

Code:
strHTML = strHTML & Replace(strLine, "DearName", rsE1!DearName)

But I'm not sure how to build that into your procedure below:

Code:
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile("c:\database\test.html")
Set ts = f.OpenAsTextStream(1, TristateUseDefault)
Do While ts.readLine <> ""
strHTML = strHTML & ts.readLine
Loop

I tested the Replace function in my original "Do While Not EOF(1)" code and it worked...

The other problem that I have now is that the body text is now appearing twice in both emails - and the different text boxes are all over the place in the email body.

I don't think I specified this before, but the report I'm generating the HTML emails from is unbound - if that makes any difference?

Thanks again for all your help and patience! :cool:
 

AccessMSSQL

Seasoned Programmer-ette
Local time
Yesterday, 17:26
Joined
May 3, 2012
Messages
636
Make sure you are not appending strHTML to itself after the replace function. IF you are using strHTML = strHTML & replace(...etc you are going to have it doubled up). All you need to do is this:
strHTML = replace(strHTML, "DearName", rsE1!DearName)
at the end.
 

JeffBarker

Registered User.
Local time
Today, 00:26
Joined
Dec 7, 2010
Messages
130
Make sure you are not appending strHTML to itself after the replace function. IF you are using strHTML = strHTML & replace(...etc you are going to have it doubled up). All you need to do is this:
strHTML = replace(strHTML, "DearName", rsE1!DearName)
at the end.

Hey, thanks for this!

So I just replaced:

Code:
strHTML = strHTML & ts.readLine

With this:

Code:
strHTML = replace(strHTML, "DearName", rsE1!DearName)

And the emails fire up with the correct EmailTo and Subject, but the actual body text is blank.

Also, when I run the code I'm now getting 'Compile Error: User-defined type not defined' pointing to Dim Report as DAO.Report - I'm guessing there's another reference I need to...ah, reference...? :)
 

AccessMSSQL

Seasoned Programmer-ette
Local time
Yesterday, 17:26
Joined
May 3, 2012
Messages
636
Dim Report as string (sorry DAO.Report isn't correct)

As for the other issue, use the replace function after the read loop
Code:
do while ts.read <> ""
strHTML = strHTML & ts.readLine
Loop
strHTML = replace(strHTML, "DearName", rsE1!DearName)
 
Last edited:

Users who are viewing this thread

Top Bottom