Convert your macro to code, first of all, and call the module instead of the macro -- always better for debugging, and possibly necessary here. I think you need to loop through a table or query instead of an interated loop. You need to open that table/query as a recordset and then have a
While Not rstRecords.EOF . . . Wend loop to go through all the records. Your macro is producing the same output each time, just sending it to the next person. However, your code for the e-mail looks like it fills my need -- thanks! Tobi -----Original Message----- From: AccessDevelopers@yahoogroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Brian Stenglein Sent: Thursday, August 11, 2005 12:27 PM To: AccessDevelopers@yahoogroups.com Subject: [AccessDevelopers] Re: E-mail with spreadsheet attachment 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 AccessDevelopers@yahoogroups.com, "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: AccessDevelopers@yahoogroups.com > [mailto:[EMAIL PROTECTED] On Behalf Of Hoffman, Tobi K > (DYS) > Sent: Tuesday, August 09, 2005 10:24 AM > To: AccessDevelopers@yahoogroups.com > 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=12hnk5oas/M=362335.6886445.7839731.1510227/D=groups/S=1705115370:TM/Y=YAHOO/EXP=1123785324/A=2894361/R=0/SIG=13jmebhbo/*http://www.networkforgood.org/topics/education/digitaldivide/?source=YAHOO&cmpgn=GRP&RTP=http://groups.yahoo.com/";>In low income neighborhoods, 84% do not own computers. At Network for Good, help bridge the Digital Divide!</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/
