SendObject Method: Make "To" dynamic 1

[HenryAnthony]

Hello all!

Would anyone have any information on how to make the "To" part of SendObject be dynamic. For instance, I have a subform that lists project stakeholders that need to get information weekly on project status. Stakeholders change from project to project. How can I use the stakeholders e-mail addresses as a variable in the "To" part of the SendObject Method?

Regards and thanks for any help,

Henry

 

[kathryn]

The following line is from the SendObject Example from the help file:

DoCmd.SendObject acSendTable, "Employees", acFormatXLS, _
"Nancy Davolio; Andrew Fuller", "Joan Weber", , _
"Current Spreadsheet of Employees", , False

To change the To: to dynamic, you need to get the person's name into a variable, call it strRecipient.

then use the above line like this:

DoCmd.SendObject acSendTable, "Employees", acFormatXLS, _
strRecipient, "Joan Weber", , _
"Current Spreadsheet of Employees", , False

If the data is in a subform, you may want to use the Forms!FormName!SubformName!Textbox name format as follows

DoCmd.SendObject acSendTable, "Employees", acFormatXLS, _
Forms!YourMainFormName!YourSubFormName!TextBoxName, "Joan Weber", , _
"Current Spreadsheet of Employees", , False



Kathryn

 

[HenryAnthony]

Hi Kathryn,

Thanks for the advice. I can get your code to work but just for one recipient. What I can't do is to get the variable to include multiple recipients separated by a semicolon. Any ideas?

Regards,

Henry

 

[kathryn]

OK, where are you getting the names from?

You might need to use the format

strRecipient1 & "; " & strRecipient2 & "; " & strRecipient3

this will build the string in the proper format for you.

Kathryn

 

[HenryAnthony]

FYI Kathryn, the e-mail application is Novell Groupwise.

 

[HenryAnthony]

Hi Kathryn,

You asked, "OK, where are you getting the names from?" and that has gotten my wheels turning. Unfortunately I have many deadlines today. I need to think this through and I will post a definitive answer soon. And Kathryn, I REALLY, REALLY appreciate you helping me puzzle this out.

Have a great weekend,

Henry

 

[HenryAnthony]

Hi Kathryn,

You asked, "OK, where are you getting the names from?" I hope my answer provides sufficient information and is clear enough:

I have two tables, one table with contact names and e-mail addresses and another table of projects. These tables are related as many-to-many through a junction table.

There is a main form based on the projects table and a subform based on a query that includes all three aforementioned tables. The linked fields are the ProjectID in the projects table and the junction table. So, for each project, I can have many e-mail addresses. As I move through project records, the e-mail addresses change.

Thank you for any help,

Henry

 

[kathryn]

Henry,

You should be able to put a command button, call it cmdPrintEmails, on your Main form that prints your emails.

First create a query, call it qryEmailAddresses, that queries your contact table. Include the email address and the ProjectID. In the criteria line, under projectID, enter
[ID]
That will make the query a parameter query.

The code would look like this:


Sub cmdPrintEmails_Click()

dim qdf as querydef
dim rst as recordset

set qdf=currentdb.querydefs("qryEmailAddresses&quot
qdf.parameters!("ID&quot = me!projectID 'fills the parameter
set rst=qdf.openrecordset

rst.movefirst

do until rst.eof

DoCmd.SendObject acSendTable, "Employees", acFormatXLS, _
rst!YourEmailAddressFieldName, "", , _
"YourEmailSubject", , False

rst.movenext
loop

rst.close
set rst=nothing
qdf.close
set qdf=nothing


End Sub


This should get you started. Let me know how it goes
Kathryn

 

[HenryAnthony]

Mentor,

Kathryn, your code works great. The amazing thing is I (a graphic designer) can even understand it. The only problem occurs when I open the form I am prompted for the ProjectID. I solved it by using a temporary query definition that includes the parameter that the code fills. Thanks for helping me with this. If you ever have any design issues regarding Photoshop, 3D, etc. please feel free to e-mail me at stasekh@trinity-health.org, maybe I could help. Thanks again. I will be looking for your posts in this forum.

Henry