E-mail using Query with Microsoft Outlook

Question

Post reply

E-mail using Query with Microsoft Outlook

C-227852

Grasshopper

Points: 14
More actions
May 1, 2005 at 2:43 am

#65808

Each month I make a file, with [Ship To], [Ship From] [Number of shipments], [Date of Shipment], [Weight], [Charge].
I send this information to different people using the country [Ship to] they are in charge of and the month they want the information for. So for example I send everything related to Belgium for April 2004 to Mr X, Holland for March 2005 to Mr Y.
How do I choose the country and the month a person wants and send it to the person in charge of that country?
If anyone can help with this query I will be very grateful.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 1

"How do I choose the country and the month a person wants and send it to the person in charge of that country?"

Create another table "ShippingRequest" with "ShipTo", "ShipFrom" and "ShipMonth".

Change your query to join with this new table.

...ShippingRequest SR on x.ShipFrom = SR.ShipTo

jfmccabe SSCrazy Points: 2547 More actions · Answer 2

I presume you have a table linking ShipTo, Person and EmailAddress - tRecipients.

It is then a simple matter to open this table in code and to step through it selecting the data you want and using sendobject to send the e-mail. I would assume that you have a suitable report defined - rCountry.

Dim rs As New ADODB.Recordset rs.Open "tRecipients", CurrentProject.Connection, adOpenKeyset, adLockOptimistic DoCmd.OpenReport "rCountry", acViewDesign, , , acHidden Do While Not rs.EOF Reports.rst.Filter = "ShipTo='" & rs("ShipTo") & "' AND [Date of Shipment] between " & StartDate & _
" and " & EndDate DoCmd.SendObject acSendReport, "rCountry", acFormatRTF, rs("EmailAddress "), , , _ "Shipping Report", "The attached is the shipping data for " & Format(StartDate,"mmmm yyyy"), __
False rs.MoveNext Loop DoCmd.Close acReport, "rCountry", acSaveNo

StartDate and EndDate can be the parameters passed to the subroutine. You can also look at the xp_sendmail procedure to see how to do the same thing on the server.