Automated EMails


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,

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)
Signature = ""
End If

With OLMsg
.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'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

Anyone have ideas?

Thanks in advance.


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
then if it not open run the shell

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

Jeff G

I looked at the one on, 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.



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

will ask you some questions when i get stuck :)


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.



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.

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

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.

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