SendObject Embed SubForms in message body (1 Viewer)

IvanM · Aug 9, 2016

Hi,

I would like to be able to send an email using the SendObject method from a form that has 2 sub-forms.

Ideally, I want to be able to copy the 2 subforms and embed them in the message body as, say, an image, with text before and after the inserted images.

Also, what would be the simplest way of building the "To" string of recipients from a table containing users and their email addresses?

Thanks in advance for any help in solving this one...

Ranman256 · Aug 9, 2016

the form needs to show the list of emails. Loop thru the list and send the data in the subforms.
create a query that pulls data from both tables to create the message to send. This would be subform3, that combines all data you want to send.
Then loop thru the list sending emails.

Code:

'------------
Public Sub ScanAndEmail()
'------------
Dim vTo, vSubj, vBody, vRpt
Dim vFilePath
dim i as integer

vBody = subfrm3.txtMsg
vRpt = "rReport1"
vSubj = vRpt

     'scan the list box
For i = 0 To lstEAddrs.ListCount - 1
   vTo = lstEAddrs.Column(2)
     
   DoCmd.SendObject acSendReport, vRpt, acFormatPDF, vTO, , , vSubj, vBody
Next
End Sub

Minty · Aug 9, 2016

I don't think unless you can make a report with the sub forms in it you would be able to use the send object method. I think the easiest route would have to be to save your output to pdf to embed any images from your sub forms, assuming that's what you are trying to accomplish?

As for your email addresses a simple loop through the recordset to set the To: string will do the job, but unless they are internal email addresses I would put them in the Bcc: field and use Outlook automation to send the items.
That will also allow you to attach multiple items if the need arises.

IvanM · Aug 10, 2016

Thanks Ranman and Minty.

Yes Minty, that is what I want to achieve.

Currently the email has text top and bottom and 2 excel tables pasted in as images so that recipients can't alter them and some are able to copy and paste from the email for other reports...

I've sorted the TO address part, it was simple really, but always worth asking as I'm often impressed with the simple/elegant solutions people have for that sort of thing!

So, I still need to find a solution to copying 2 subforms as images from the current report into the message body of an email...

Minty · Aug 10, 2016

Okay - is the data in the excel sheets extracted from your database?
If it is you could simply put that into a report as 2 sub reports and save it as a pdf or send it as a pdf.
If the data is not in your database why not link to the spreadsheets and create the report from the linked data do the same?

IvanM · Aug 10, 2016

Hi Minty,

the email currently contains 2 images copied from an excel sheet (and no, not exported from my DB), I'm building the DB to automate this and many other things, so the email I'll be creating will be from the 2 subforms.
The easiest route would have been a PDF attachment but I've been told the images have to be embedded within the message body.

Any further ideas?

Cheers,
Ivan

Minty · Aug 10, 2016

This sounds more than awkward from so many angles.

How is the excel image generated ?
I'm guessing it would be stored differently on each users PC. You will have to code it to check that the images are stored and named in exactly the same consistent way to enable you to automatically embed it.
I'm pretty sure the only way to embed the image would be in HTML, and that is very messy to code with images.
I keep going back to the fact that if the data is in excel, it therefore could / should be in access and then easily presented as part of the email

IvanM · Aug 10, 2016

1. How is the excel image generated ?
A. Simply copied (the relevant area is selected) from the spreadsheet and pasted into the body of the email.

2. I'm guessing it would be stored differently on each users PC. You will have to code it to check that the images are stored and named in exactly the same consistent way to enable you to automatically embed it.
A. It's only produced by 3 possible users, all from the same spreadsheet.

3. I'm pretty sure the only way to embed the image would be in HTML, and that is very messy to code with images.
A. Agreed, but I suspect this is going to be the way to go, somehow save as an image on shared area of the network or locally then added to the HTML for the email perhaps? I was hoping there was some way of copying to the clipboard then pasting from that...this would require clipboard to hold the 2 copies however...

4. I keep going back to the fact that if the data is in excel, it therefore could / should be in access and then easily presented as part of the email
A. Not sure exactly what you mean by this?

Minty · Aug 10, 2016

IvanM said:
4. I keep going back to the fact that if the data is in excel, it therefore could / should be in access and then easily presented as part of the email
A. Not sure exactly what you mean by this?

If you have the data in a central spreadsheet that you are effectively taking a picture of, you could link Access to that spreadsheet and then pull the spreadsheet data into a suitable query and create a email with that data in it directly. No need for the embedded images.

IvanM · Aug 12, 2016

Hi Minty,

I'm replacing the spreadsheet with the DB. The data is currently copied and pasted into the spreadsheet from another system. It's not just about the data itself, there are 2 separate snapshot images that are pasted into the email (and it needs to continue this way).
There's also specific formatting (including conditional) in the 2 table spreadsheets, which I've replicated on the 2 subforms.
Guess I'll have to brush up on the HTML....

SendObject Embed SubForms in message body (1 Viewer)

IvanM

Registered User.

Ranman256

Well-known member

Minty

AWF VIP

IvanM

Registered User.

Minty

AWF VIP

IvanM

Registered User.

Minty

AWF VIP

IvanM

Registered User.

Minty

AWF VIP

IvanM

Registered User.

Similar threads

Users who are viewing this thread