Automated EMails

J

Jeff G

All -

I have just ran into an issue that is making my head spin. I have a process
that imports a CSV file, manipulates it, exports an Excel query and e-mails
the query. Snippets are listed below:

Dim strFolder As String
Dim Signature As String, SigString As String
Dim OLApp As Outlook.Application
Dim OLMsg As Outlook.MailItem
....
....
....
DoCmd.OutputTo acOutputQuery, "QueryName", acFormatXLS,
"C:\Temp\DatabaseExport\ExcelFile.xls"

Set OLApp = New Outlook.Application
Set OLMsg = OLApp.CreateItem(olMailItem)

SigString = "C:\Documents and Settings\" & Environ("username") &
"\Application Data\Microsoft\Signatures\Default.htm"

If Dir(SigString) <> "" Then
Signature = Get_Signature(SigString)
Else
Signature = ""
End If

With OLMsg
.Display
.To = "(e-mail address removed)"
.Subject = "Message Subject"
.HTMLBody = "<html><body>" & "Please see the attached file for ..." &
"<html><br><br><br></body></html>" & Signature

.Attachments.Add "C:\Temp\DatabaseExport\ExcelFile" & ".xls"
Kill "C:\Temp\DatabaseExport\ExcelFile.xls"
End With

Set OLMsg = Nothing
Set OLApp = Nothing

Now...here's the part I don't understand. All works fine as long as Outlook
is open. If it is not, I get everything EXCEPT the attachment in the new
e-mail.

Anyone have ideas?

Thanks in advance.
 
W

Wayne-I-M

Hi Jeff

You could shell the path to outlook in the code

Something like
Call Shell("C:\Program Files\Microsoft Office\OFFICE11\OUTLOOK.EXE")

Or - more what I tend to do is 1st see if outlook is open - one methodd is
shown here http://www.mvps.org/access/api/api0007.htm
then if it not open run the shell

or
Just an idea that I have sene others using us t set outlook as a variable
something like

Dim objOutlook As Object
Set outOutlook = GetObject(, "Outlook.Application")
MsgBox "Its already open", vbInformation, "MS OutLook"

if not (else) then shell again

Give em a try and see what you think
 
J

Jeff G

I looked at the one on www.mvps.org, but I didn't see what arguement to use
for Outlook.

The Call Shell method will work, I just had to modify it for '07.

Thanks.
 
W

Wayne-I-M

you know more than me then - just started to get to grips with 2k7 - OMG

will ask you some questions when i get stuck :)
 
M

mscott2006

Whenever I want to attach an object in e-mail I use the SendObject command in
a macro. I am not sure if you even use macros but tt always opens Outlook,
provided it is the default e-mail program, and attaches the object specified.
However, with this method you need to select the Edit option as security
keeps it from being sent automatically. If it must be done unattended, you
would have to write code. Not sure if this helps you.
 
W

Wayne-I-M

Hi

There are some ("very" few) times when macros are a good idea. They work
fine "if" everything is ok but you can't add error handeling, etc. So macros
in this case are not really a good idea. Just my idea.
 
A

Arvin Meyer MVP

SendObject works fine for Access objects, or an object created by the
process, but it will not send other attachments, nor will it send multiple
attachments.

Further, macros cannot be made to check for things like file existence and
handle errors should they not exist, so they are not a very robust method
for doing serious database work.
 
J

Jeff G

Here's what I ended up using:


Set objOutlook = GetObject(, "Outlook.Application") ' Determine if
Outlook is open.

If Err <> 0 Then
Call Shell("C:\Program Files\Microsoft
Office\OFFICE12\OUTLOOK.EXE")
End If


I had to add an On Error statement
On Error Resume Next
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Range for Body of E-Mail 1
Problem sending mail from Access 2
Sending email 1
How to send email to list of individuals 7
Adding an attachment to existing email 2
Access to Outlook 5
Monthly E-Mails 8
Setting Outlook Parameters in VBA 4

Top