Email Automation from Form

[ryancgarrett]

I'm trying to set up some simple email automation to save my warehouse employees from having to write the same email to different customers multiple times per day. When they email a customer the message tells the customer the shipment ID and then the weights and dimensions for each box within the shipment. An example of the text in the email (including formatting) would be:

Shipment 4381358
Box 1 - 12" x 12" x 16" (13 lbs)
Box 2 - 16" x 18" x 18" (34 lbs)

Shipment 4389524
Box 1 - 8" x 8" x 8" (2 lbs)
Box 2 - 8" x 8" x 8" (2 lbs)
Box 3 - 12" x 12" x 16" (24 lbs)

I have the macro written to send the message from Outlook, and now I'm trying to design the form/subform (I know that's a bit backwards, but I wanted to make sure I could figure out the email automation since I hadn't done that before).



My question is, how should I store the data so that the structure makes sense, and then what is the best way to feed that to the Outlook message via VBA? I'm getting hung up on the variability of my data. Some orders will have only 1 shipment ID, whereas others will have up to 5. Some shipments will have 1 box, and others will have up to 100.

Any and all suggestions are very much appreciated. I'm pretty seasoned with VBA in Excel, but Access is a new beast to me, so forgive my ignorance.

[June7]

Options:

1. open report filtered to the customer data and use SendObject method to attach report as PDF to email

2. open a recordset object in VBA and loop through records to build a string of the customer data that can be used as the email body
this approach can also use SendObject or Outlook automation

[ryancgarrett]

Thanks so much for the reply. I didn't even think about attaching the report as a PDF, but that seems like the best/easiest option in this situation. One more super newb question, just to make sure I'm on the right track:

Should I store the Orders, Shipments, and Boxes all in separate tables with a one to many relationship between each (one order can have multiple shipments, and one shipment can have multiple boxes), or should I combine the shipments and boxes tables to avoid having my data too spread out?

Thanks again for the help!

[June7]

I think normalization calls for separate tables. Data 'spread out' should not be a deciding factor.