Emailing from a Module within a macro

[Bunky]

I am trying to send a message within an Access Module within an Access Macro
after doing some other work. Everything works
well when I am in debug but when I try to run in a macro I get an error. I
have saved the code below in a module named 'Send File to Joy' and then do a
'RunCode' with 'Send File to Joy()' in the Function Name. The error I am
getting is 'Micorsoft Office Access can't find the name 'Send' you entered in
the expression.'

What am I doing wrong?

Module code below:

Private Sub Joy_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "Query for Joy
", "\\bxgfs1\shared\Member Comm\AddrChange\Arvact-list" & Format(Date,
"mm-dd-yy") & ".xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "Query for Joy
", "\\iocc-server\dcs\address Changes\Backups for the weekly address
file\Joy's Excel File\2008\Arvact-list" & Format(Date, "mm-dd-yy") & ".xls",
True


MsgBox "File has been transfered to Boca's Shared Drive & backup Server. ",
vbOKOnly, ""

On Error GoTo Err_Joy_Click

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

With objEmail
.To = "(e-mail address removed)"
.Subject = "Joy-the Arvact list is ready."
.body = ""
.Send
End With

Exit_Joy_Click:
Exit Sub

Err_Joy_Click:
MsgBox Err.Description
Resume Exit_Joy_Click

End Sub

 

[Michael Bauer [MVP - Outlook]]

The procedure name you're showing is "Joy_Click", so you probably have to
call that. But for being able to call that it must be Public, not Private.

For more Outlook VBA related questions, please visit
microsoft.public.outlook.program_vba

--
Best regards
Michael Bauer - MVP Outlook
Use Outlook Categories? This is Your Tool:
<http://www.vboffice.net/product.html?id=2006063&cmd=detail&lang=en&pub=6>

Am Mon, 10 Mar 2008 10:57:02 -0700 schrieb Bunky: