It just so happens that I was asked to create a program to do
something like this yesterday. I have a query with the desired
results. I created a make table query from that query to produce a
table for exporting. I have a macro named "Export Spreadsheet" that
runs the make table query and runs the transfer spreadsheet command
to put the file in the proper location. I have a loop set up to run
this series of commands once for each record in a form that is
displayed. The form displays the recipient name and e-mail address
along with the sales rep code that is used as a parameter in the
query producing the desired data.
At the end of each loop I have a GoToRecord command to go to the next
record on the form. Which I thought would make the query run for the
next Rep Code and display the next Rep Name and e-mail address in the
Outlook Item, but as it runs it keeps showing the data for the 1st
rep for each iteration of the loop, even though the record selector
indicator shows that it is moving down the list.
If I Rem out the looping portion of the function and click the button
to launch it manually each time the program works as expected. Does
anyone have an idea how to make these commands reflect the current
record when it's running as a loop?
Const Base_Path As String = "F:\Users\Sales and Mktg Quote
Tracker\temp.xls"
Dim Counter As Integer
For Counter = 1 To x
DoCmd.RunMacro "Export Spreadsheet"
Set myOLApp = CreateObject("Outlook.Application")
Set myitem = myOLApp.CreateItem(olMailItem)
With myitem
.to = Forms![reps to send to form]![EMailAddress]
.subject = Forms![reps to send to form]![Name] & "Daily Quote
Recap"
.Body = "Here is your daily quote recap for: " & Date - 1
.attachments.Add Base_Path
End With
myitem.display
If Counter < x Then
DoCmd.GoToRecord , , acNext
Forms![reps to send to form]!SalesRepCode.SetFocus
End If
Next Counter
Thanks,
Brian Stenglein
--- In [email protected], "Eric Douglas"
<[EMAIL PROTECTED]> wrote:
> One thing we do here is to:
>
> 1) create a temp directory
> 2) output the query as an Excel spreadsheet, and save it in the temp
> directory
> 3) Attach it to an e-mail
>
> 'put this in the form code:
> Const Base_path As String = "C:\Temp123456789"
>
> 'put this behind the button to perform the action
> MkDir Base_path
>
> 'we have this in its own module
> 'Create the OUTLOOK OBJ
> Set myOLApp = CreateObject("Outlook.Application")
> Set myitem = myOLApp.CreateItem(olMailItem)
> 'Add Information to the EMAIL
> With myitem
> .To = "" 'To: field here
> .Subject = "" 'Subject of e-mail here
> .Body = "" 'Body of e-mail here
> DoCmd.OutputTo acReport, "YourReportName",
acFormatXLS,
> Base_path & "\FileoutputName.xls", False, "", 0
> .Attachments.Add Base_path & "\FileoutputName.xls"
> .Save 'Puts them in the Draft Box - I think you can
do .Send
> here as well
> End With
> 'Clear the OBJ out
> Set myOLApp = Nothing
> Set myitem = Nothing
> RmDir base_path
>
> Hope that helps. There is some extra stuff I took out - error-
checking,
> and where we set the variables for the To, Subject, Body and report
> name, but I know you can do that stuff easily.
>
> Eric
>
>
>
> ________________________________
>
> From: [email protected]
> [mailto:[EMAIL PROTECTED] On Behalf Of Hoffman,
Tobi K
> (DYS)
> Sent: Tuesday, August 09, 2005 10:24 AM
> To: [email protected]
> Subject: [AccessDevelopers] E-mail with spreadsheet attachment
>
>
>
> I've been searching the archive (and BTW, the search procedure
> has been much improved since the last time I did this!) to find if
> anybody ever addressed the issue of sending an e-mail with an
attachment
> via e-mail. I have generated a spreadsheet to export data, and the
> client wants it to be sent automatically via e-mail to the central
> office. The DoCmd.SendObject method allows you to send an Access
> object, such as a table or query, but what I need to do is send the
> spreadsheet that I have just created. I could send the whole list
of
> queries that went into the spreadsheet, but I'd prefer to do it as a
> single e-mail with the spreadsheet attached.
>
> Tobi Hoffman
> Dept. of Youth Services
> Boston, MA
------------------------ Yahoo! Groups Sponsor --------------------~-->
<font face=arial size=-1><a
href="http://us.ard.yahoo.com/SIG=12h6547gr/M=362131.6882499.7825260.1510227/D=groups/S=1705115370:TM/Y=YAHOO/EXP=1123784819/A=2889191/R=0/SIG=10r90krvo/*http://www.thebeehive.org
">Get Bzzzy! (real tools to help you find a job) Welcome to the Sweet Life
- brought to you by One Economy</a>.</font>
--------------------------------------------------------------------~->
Please zip all files prior to uploading to Files section.
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/AccessDevelopers/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
