newbie - email from access 97 (office 97)

**JonTom** · 2004-06-30 23:19

I have found some info in these boards on how to do this, but it is a little (or a lot!) over my head...

I have a form that I want to email. I want the email to look the same as the form does in Access97, so I thought I would use a PDF of it as an email attachment. I use PDF995 to create the PDF, but that's open to change (as is the pdf itself, if anyone has a better suggestion...)

I want to build a button to take the form (filtered to one record only), print it with PDF995 using the reservation number ("Reservation Number") in the record as the file name (automatically if possible to a non-changing directory), then take the email address from text box "E-mail Address" on the form, automatically create an email with this address, pdf attachment, and subject "Your Confirmation" (this doesn't need to change).

I'm using Access and Outlook 97. I am slowly learning to us the macros in A97, but all the talk of code, modules, subs, etc, blows by me. I would appreciate any help that you all would like to give someone who is learning!

Thanks in advance,

Jon

BTW - I may also want to be able to fax these forms via fax-modem at a later date. If this is something that is easy to incorporate with this problem, feel free to solve two birds with one stone!!!

Thanks

**HansV** · 2004-07-01 21:44

Welcome to Woody's Lounge.

You have a bit of a problem here. As you found out from looking around the forums, what you want to do requires you to be familiar with Access and Outlook VBA; controlling one application from another in code is fairly complex and not really something for a beginner to tackle. This makes it hard for us to answer your questions. So I think it would be wise to learn some VBA programming in Access first, either by following a course, or by studying one of the many books for beginners. Feel free to come back with specific questions.

**JonTom** · 2004-07-01 22:56

Yeah, i was afraid someone would say that [img]/forums/images/smilies/smile.gif[/img]

I have gotten somewhere... I can make the pdf okay (filtered the form and printed it - i have to add the filename manually, but so be it), and launch a new email message with the following code:

Private Sub Command393_Click()
On Error GoTo Err_Command393_Click

Dim stAppName As String

stAppName = "C:Program FilesMicrosoft OfficeOfficeoutlook.exe /c ipm.note /m mailto:?subject=Your Confirmation from the Whistler Resort and Club&body=This is your confirmation for your Whistler Vacation. You will need Adobe Acrobat to open this attachment. It is available at http://www.adobe.com/products/acrobat/readstep2.html"

Call Shell(stAppName, 1)

Exit_Command393_Click:
Exit Sub

Err_Command393_Click:
MsgBox Err.Description
Resume Exit_Command393_Click

End Sub

This probably isn't the most elegant way to do this, but it's working except for one problem - all the text (subject and body) come out in lower case in the email. How do I specify UPPERCASE? Is it something like the "percent20" instead of space?

Of course, I need to manually address the email and do the attachment, but I'm getting closer. Any help would be appreciated!

Thanks

**HansV** · 2004-07-01 23:08

You don't need Shell to create an e-mail message, you can use the built-in command DoCmd.SendObject:

Private Sub Command393_Click()
On Error GoTo ErrHandler

DoCmd.SendObject _
To:="someone@somewhere.com", _
Subject:="Your Confirmation from the Whistler Resort and Club", _
MessageText:="This is your confirmation for your Whistler Vacation. " & vbCrLf & _
"You will need Adobe Reader to open this attachment. " & vbCrLf & _
"It is available at http://www.adobe.com/products/acrobat/readstep2.html", _
EditMessage:=True
Exit Sub

ErrHandler:
If Err <> 2501 Then ' Ignore error 2501 = canceled.
MsgBox Err.Description, vbExclamation
End If
End Sub

You would still have to add the attachment manually. DoCmd.SendMail does not provide for sending external attachments.

**JonTom** · 2004-07-02 00:11

Yes, thank you. This is working!

Jon

**JonTom** · 2004-07-07 21:21

excellent. Things are moving along nicely!

I have succesfully modified your code above to bring the email address from the form into the to: field. Now one more thing will put this project to bed...

PDF995 has a function where it can autoname the pdf with the name of the object being printed. This results in all pdfs being named 'email conf.pdf'. How can I change the name of the form being printed to reflect the reservation number in the form? FYI - the printed form is "email conf" and the button that starts pdf995 is on "res form2".

I tried to use DoCmd.Rename forms![res form2].[reservation number], acForm, [email conf], but got an error stating that access couldn't find "email conf". It is there - argh!

I assume that i will be able to reverse the naming procedure to return the form name to "email conf" after the printing is completed.

Thanks
Jon

**HansV** · 2004-07-07 23:20

The first and last arguments to DoCmd.Rename are strings, so the last argument must be <code>"email conf"</code>, not <code>[email conf]</code>. If you use <code>forms![res form2].[reservation number]</code> as new name, you'll just get the reservation number as name, nothing else. Is that OK?

**JonTom** · 2004-07-08 22:02

Yes, that is exactly what I want. Thanks for that.

I have attached the sub that I am using as I am still having one small problem. Everything works fine except the explorer window that I am opening a) needs to be refreshed to show the new pdf at the top of the list, and [img]/forums/images/smilies/cool.gif[/img] opens, but then is hidden behind the access and email windows. Is there a way to refresh the window and force it to the top so I can drag the attachment to the email? I tried putting the shell command after the SendObject, but it seems to wait for the SendObject to finish before opening explorer.

I am starting to understand what I'm doing and the code I am writing/modifying. Very satisfying feeling!

**HansV** · 2004-07-08 22:23

You call Shell with the second argument set to 4, i.e. WindowStyle = vbNormalNoFocus. Use vbNormalFocus (=1) or vbMaximizedFocus (=3) instead to bring Windows Explorer to the foreground. The subsequent SenObject will display the e-mail in front of it, but at least Windows Explorer will not be hidden behind Access.
If you set up Windows Explorer to show files in reverse Modified order (newest to oldest), the PDF file will be at the top.

**JonTom** · 2004-07-08 22:29

I tried the alternate focus arguments, but they made no difference in the end - Access, then the email windows still cover the explorer window.

I already have the sort order set as recomended, but I need to refresh the list for it to work. Otherwise the new pdf is left at the end (or sometimes in the middle?).

**HansV** · 2004-07-08 22:47

I don't know of an easy way to remedy this.

If you're feeling to start more confident with code, and if you are using Outlook, you might consider taking the next step - automate Outlook from Access. If you want to experiment, start by making a copy of your database, and using that. If things go wrong, you can always go back to the version you have now.

To enable manipulating Outlook, open any code module, and select Tools | References...
Locate the Microsoft Outlook n.m Object Library or Microsoft Outlook n.m Object Model. n.m will be 8.0 for Outlook 97, 8.5 for Outlook 98.
Tick the corresponding check box and click OK.

See the thread starting at <post#=383300>post 383300</post#> for an example of creating an e-mail in Outlook and attaching a PDF file in code.

**JonTom** · 2004-07-08 22:53

Yes, I have already copied the db and started experimenting with this [img]/forums/images/smilies/smile.gif[/img] I did want to get this solution working perfectly in the meantime, but it is working now, so I that will do!

Thanks for all your help this week, Hans! I'm sure I will have many more questions as I start to explore Automation...

Jon

Thread: newbie - email from access 97 (office 97)

Thread Tools