Create one email (htmlbody) from multiple records 1

[Lokoono]

I searched for an answer to this question, but I think I'm pretty unique in asking.

I need to send 5 emails to the users containing 10 records (based off the subform) for each user.

I can't figure out how to temporarily interrupt the htmlbody code in Access to allow me to go to the next record in the subform and then to go back to the htmlbody to create the next few lines of the email.

In essence, I want to do this:

.htmlbody="Results for February:  " & [txtResults1]
'Stopping htmlbody to perform form commands
subfrmResults.SetFocus
docmd.GoToNextRecord , , acNext
me.SetFocus
'Restarting htmlbody
.htmlBody="<br><br>" &_
"Results For March: &nbsp;" & [txtResults1]
'Stopping htmlbody to perform form commands
subfrmResults.SetFocus
docmd.GoToNextRecord , , acNext
me.SetFocus
etc....

I have to use htmlbody because I'm creating memos for the users with html tables integrated into them.


My main question is:

Is there a way to be in the middle of creating an htmlbody, send out form commands, and then go back to the htmlbody to finish the email?

Secondary question:

I can do a workaround if needed and just use variants to temporarily store the data from each record and then create the email. How many variants will a procedure allow me to use though? I'm looking at about 80 to 100 with this process.


Oh...and I know I could just create a report to show all of this (and I have - I just can't email it with all the formatting). The recipients don't have snapshot viewer or Adobe Acrobat (and I'm not allowed to distribute either). They just have Outlook Web Access. RTF and HTML exports that are imbedded in the email's body won't work either since the rectangles in the Access report don't convert over.

So I'm stuck to either getting a miraculous solution to my main question or just using the workaround.


Thanks for any help you can offer.

 

[ck1999]

you could look at assigning a global variable (vhtmlbody) and then add to the variable until you are ready to send and then create a procedure to send the email. When you are ready to send the email just call this procedure.

Just a thought but could you use a query to get your information and then loop through the records in the query and create your email from that. This may or maynot be easier depending on your subform.

ck1999

 

[Lokoono]

Hmmm...good points ck1999.

I have If...Then statements for each particular record unfortunately. If the month is February, certain records go in the email and if the month is March, other records go in the email...etc.

I'm not familiar with global variables (yet) or using a loop through a query (although I have done loops through forms). Would I be able to do either if I'm incorporating If...Then statements to help create the email?

Let me know your thoughts and I'll reply as quick as I can. Again, I appreciate the help.

 

[ck1999]

a global variable is declared in the vba window before other code

for example:

dim vhtmlbody 'Notice this line is above all other code

sub form_load
vhtmlbody = ""
' stuff
end sub

sub cmdButton1_click()
vhtmlbody = vhtmlbody & txtresults & Chr(10)
end sub

try something like this then replace your code

.htmlbody="Results for February:  " & [txtResults1]
'Stopping htmlbody to perform form commands
subfrmResults.SetFocus
docmd.GoToNextRecord , , acNext
me.SetFocus
'Restarting htmlbody
.htmlBody="<br><br>" &_
"Results For March:  &nbsp;" & [txtResults1]
'Stopping htmlbody to perform form commands
subfrmResults.SetFocus
docmd.GoToNextRecord , , acNext
me.SetFocus

with

.htmlbody = vhtmlbody

See how this works


Your other point
the WHERE statement in the query should be able to separate out which records go with which month. if you decide to use this option at a later time

ck1999

 

[Lokoono]

ck1999,

Your example works great. I feel better now about what I need to do and how I can get there.

I'm thinking your looping query solution would work too (using WHERE statements when needed). I'll look into trying that next (as a way for me to compare which solution works quicker and can be easier updatable for me).

Thanks for your great and quick help!