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:
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.
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.