Send report to email & insert email address off current form (1 Viewer)

MichaelWaimauku · Dec 6, 2012

I have the following vba that takes current data off a form and puts it into a report:

Private Sub Command25_Click()
DoCmd.OpenReport "nonconformity", acViewPreview, , "[nonconformid]=" & Me.NonConformID
End Sub

I want to take that report and have it attached to an email as pdf doc. Also, the to: in the email address to be populated by the field on the current form. All driven from the button on the open form. Is this possible & how?

The field holding the email address is forms![non conformity]![contact]

Alansidman · Dec 6, 2012

This link may be helpful if you are using Outlook

http://www.access-programmers.co.uk/forums/showthread.php?t=237841

MichaelWaimauku · Dec 6, 2012

Hi Alan

I looked at the code but I don't want it to email right away. I want the report that is generated by the code above to become an attachment to a 'New Mail Message' (using Outlook) and for the email to remain open so some user defined data can be entered into the body. Does that make sense?

pbaldy · Dec 6, 2012

Change

.send

to

.Display

MichaelWaimauku · Dec 6, 2012

Brilliant, now how to get normal default signature to display. With current code it just sets up a blank email without default user signature.

MichaelWaimauku · Dec 7, 2012

Alansidman said:
This link may be helpful if you are using Outlook

That's very helpful, but how do I get a report in print preview, generated from this code as an attachment at the same time?

Private Sub Command25_Click()
DoCmd.OpenReport "nonconformity", acViewPreview, , "[nonconformid] =" & Me.NonConformID
End Sub

Also, if I was going to change the body of the text to normal text from HTML, how do I do that? I think that's stopping the email signature from appearing.

Really stumped on these two things... HELP

pbaldy · Dec 7, 2012

Change

.HTMLBody = "Text"

to

.Body = "Text"

As to the attachment, using this method you have to create the attachment as a file and then attach it. Simpler to use SendObject after opening in preview mode, which should apply the wherecondition to the attachment. To stick with this you'll need OutputTo to create a file and adapt the code from here to attach it:

http://support.microsoft.com/?kbid=161088

MichaelWaimauku · Dec 7, 2012

Sorry I'm a complete novice. I have no idea how to begin writing this. Where in the following text does SendObject get placed and how do I complete the wherecondition? - I don't want to be saving any pdf to file location but rather straight from report preview to email.

DoCmd.OpenReport "nonconformity", acViewPreview, , "[nonconformid] =" & Me.NonConformID

The above puts the current record in preview mode but then where is this inserted in relation to the following & how do I get it into either body or attachment?

Private Sub Command26_Click()
Dim olApp As Object
Dim objMail As Object
On Error Resume Next 'Keep going if there is an error

Set olApp = GetObject(, "Outlook.Application") 'See if Outlook is open

If Err Then 'Outlook is not open
Set olApp = CreateObject("Outlook.Application") 'Create a new instance of Outlook
End If
'Create e-mail item
Set objMail = olApp.CreateItem(olMailItem)
With objMail
'Set body format to HTML
.BodyFormat = olFormatHTML
.To = Forms![non conformity]![Contact]
.Subject = "Task Assigned"
.Body = "Text"
.display
End With
MsgBox "Operation completed successfully"
End Sub

ANOTHER option would be to pull off individual fields from the form in display and populate the body of the email. If this is easier, how do I put items on individual lines using vba in the body?

pbaldy · Dec 7, 2012

Like I said, you would switch to using SendObject:

DoCmd.OpenReport...
DoCmd.SendObject...

To pull fields off a form, presuming it's the same form the code is in, this type of thing:

.Body = "Dear " & Me.FirstName & "," & vbCrLf & "More text here"

vbCrLf inserts a line feed. Generally I'd use a variable if building a more complicated body.

MichaelWaimauku · Dec 9, 2012

pbaldy said:
Like I said, you would switch to using SendObject:

DoCmd.OpenReport...
DoCmd.SendObject...

Are you able to help me complete these commands, as mentioned I'm new to this and not sure how to enter the various spots in the commands.
I entered in the following details for the first command:

DoCmd.OpenReport "nonconformity", acViewReport, , , , "[nonconformid]=" & Me.NonConformID

However, it's opening the report showing all records rather than the current one. How can I amend this to do that?
How does the SendObject take this open report and put it into the email?

pbaldy · Dec 9, 2012

Earlier you had the argument that would open in preview mode. Now you have it printing, which SendObject can't use.

MichaelWaimauku · Dec 9, 2012

I see what you mean,... so if I've got my DoCmd.OpenReport set to the following:

DoCmd.OpenReport "nonconformity", acViewPreview, , "[nonconformid] =" & Me.NonConformID

How do I use the DoCmd.SendObject to pick that open report in preview mode and place it into the email?

I'd like to try and get this nailed today if possible :banghead:

pbaldy · Dec 9, 2012

Just put the SendObject right after that line. If the report is open filtered, it should create a filtered attachment.

MichaelWaimauku · Dec 9, 2012

Also, how do I bold the underlined part of this line: & vbCrLf & "Description of Issue: " & Me.Issue_Description & vbCrLf & vbCrLf...

MichaelWaimauku · Dec 9, 2012

Thank you for your help here Paul. I've written the following code.

DoCmd.OpenReport "nonconformity", acViewPreview, , "[nonconformid] =" & Me.NonConformID
DoCmd.SendObject acSendReport, , acFormatPDF

Now if I cancel the Email it pops up with the following error: Run-time error '2501': the SendObject action was cancelled. How do I remove that error if I decide not to send the email and instead cancel it?

I've added

DoCmd.Close acReport, "NonConformity", acSaveNo

at the end but if I close the email there's the above error and of course it doesn't complete so leaves the report open.

If the email is sent, all works out well. What can I put in that if the email is not sent but rather closed, the report is closed and a dialogue box appears to say 'Message not Sent'.

pbaldy · Dec 10, 2012

You should be able to handle that with error trapping:

http://www.baldyweb.com/ErrorTrap.htm

Just leave out the message box for that error.

MichaelWaimauku · Dec 12, 2012

'Create e-mail item
Set objMail = olApp.CreateItem(olMailItem)
With objMail
'Set body format to HTML
.BodyFormat = olFormatHTML
.To = Forms![non conformity]![Contact]
.Subject = "Task Assigned"
.Body = "Text"
.display
End With
MsgBox "Operation completed successfully"
End Sub

When I run this script, the part in red has an error and I'm not sure why or how to fix? - run-time error '5' - Invalid procedure call or arguement

MichaelWaimauku · Dec 12, 2012

Someone gave this fix... Const olFormatHTML = 2

All good now

Send report to email & insert email address off current form (1 Viewer)

MichaelWaimauku

Registered User.

Alansidman

AWF VIP

MichaelWaimauku

Registered User.

pbaldy

Wino Moderator

MichaelWaimauku

Registered User.

MichaelWaimauku

Registered User.

pbaldy

Wino Moderator

MichaelWaimauku

Registered User.

pbaldy

Wino Moderator

MichaelWaimauku

Registered User.

pbaldy

Wino Moderator

MichaelWaimauku

Registered User.

pbaldy

Wino Moderator

MichaelWaimauku

Registered User.

MichaelWaimauku

Registered User.

pbaldy

Wino Moderator

MichaelWaimauku

Registered User.

MichaelWaimauku

Registered User.

Similar threads

Users who are viewing this thread