Send report to email & insert email address off current form

[MichaelWaimauku]

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]

This link may be helpful if you are using Outlook

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

 

[MichaelWaimauku]

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]

Change

.send

to

.Display

 

[MichaelWaimauku]

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]

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]

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]

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]

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]

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]

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

 

[MichaelWaimauku]

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]

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

 

[MichaelWaimauku]

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

 

[MichaelWaimauku]

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]

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]

'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]

Someone gave this fix... Const olFormatHTML = 2

All good now