email using SendObject

[Database Administrator]

I have created a command button on a form that prints a filtered report and
e-mails the report to three people when "clicked". The three email addresses
are selected on the form by three combo boxes that populate the following
three fields - "ContName", "ContName1" and "ContName2".

My code simply uses SendObject to print the report and select the three
e-mail addresses. However, I do not always have three recipients to send the
report to, I may only want to send the report to two recipients (therefore
the ContName2 field on the form will be empty). When this is the case I get
an error, obviously the code is looking for the third e-mail address.

I've attached the code, for your review, at the bottom of this post.

Is there any way around this - I want the code to ignore any "empty" field.

I've Googled and searched MS for a solution - I'm either missing the point
or I'm doing something terribly wrong.

Thanks in advance for any help

John Taylor

The code:-



Dim stDocName As String
Dim strReportFilter As String

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70


stDocName = "RptChangeControlMain"
strReportFilter = "ID = " & Forms!FrmChgCon!ID

DoCmd.OpenReport stDocName, acViewNormal, , strReportFilter

DoCmd.SendObject acSendReport, stDocName, "Snapshot Format",
Forms!FrmChgCon!ContName, Forms!FrmChgCon!ContName1,
Forms!FrmChgCon!ContName2, "Title - Header Description", "do something",
True

[Database Administrator]

On Sat, 9 Dec 2006 11:39:25 +0900, John Taylor wrote:

> I have created a command button on a form that prints a filtered report and
> e-mails the report to three people when "clicked". The three email addresses
> are selected on the form by three combo boxes that populate the following
> three fields - "ContName", "ContName1" and "ContName2".
>
> My code simply uses SendObject to print the report and select the three
> e-mail addresses. However, I do not always have three recipients to send the
> report to, I may only want to send the report to two recipients (therefore
> the ContName2 field on the form will be empty). When this is the case I get
> an error, obviously the code is looking for the third e-mail address.
>
> I've attached the code, for your review, at the bottom of this post.
>
> Is there any way around this - I want the code to ignore any "empty" field.
>
> I've Googled and searched MS for a solution - I'm either missing the point
> or I'm doing something terribly wrong.
>
> Thanks in advance for any help
>
> John Taylor
>
> The code:-
>
>
>
> Dim stDocName As String
> Dim strReportFilter As String
>
> DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
>
> stDocName = "RptChangeControlMain"
> strReportFilter = "ID = " & Forms!FrmChgCon!ID
>
> DoCmd.OpenReport stDocName, acViewNormal, , strReportFilter
>
> DoCmd.SendObject acSendReport, stDocName, "Snapshot Format",
> Forms!FrmChgCon!ContName, Forms!FrmChgCon!ContName1,
> Forms!FrmChgCon!ContName2, "Title - Header Description", "do something",
> True
>
>

Does this work?

Dim strSendTo as String
strSendTo = (Trim(Forms!FrmChgCon!ContName) + ",") &
(Trim(Forms!FrmChgCon!ContName1) + ",") &
Trim(Forms!FrmChgCon!ContName2)

If Right(strSendTo, 1) = "," Then
strSendTo = Left(strSendTo, Len(strSendTo) - 1)
End If

stDocName = "RptChangeControlMain"
strReportFilter = "ID = " & Forms!FrmChgCon!ID

DoCmd.OpenReport stDocName, acViewNormal, , strReportFilter

DoCmd.SendObject acSendReport, stDocName, "Snapshot Format",
strSendTo, "Title - Header Description", "do something",
True

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[Database Administrator]

On Sat, 9 Dec 2006 11:39:25 +0900, John Taylor wrote:

> I have created a command button on a form that prints a filtered report and
> e-mails the report to three people when "clicked". The three email addresses
> are selected on the form by three combo boxes that populate the following
> three fields - "ContName", "ContName1" and "ContName2".
>
> My code simply uses SendObject to print the report and select the three
> e-mail addresses. However, I do not always have three recipients to send the
> report to, I may only want to send the report to two recipients (therefore
> the ContName2 field on the form will be empty). When this is the case I get
> an error, obviously the code is looking for the third e-mail address.
>
> I've attached the code, for your review, at the bottom of this post.
>
> Is there any way around this - I want the code to ignore any "empty" field.
>
> I've Googled and searched MS for a solution - I'm either missing the point
> or I'm doing something terribly wrong.
>
> Thanks in advance for any help
>
> John Taylor
>
> The code:-
>
>
>
> Dim stDocName As String
> Dim strReportFilter As String
>
> DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
>
> stDocName = "RptChangeControlMain"
> strReportFilter = "ID = " & Forms!FrmChgCon!ID
>
> DoCmd.OpenReport stDocName, acViewNormal, , strReportFilter
>
> DoCmd.SendObject acSendReport, stDocName, "Snapshot Format",
> Forms!FrmChgCon!ContName, Forms!FrmChgCon!ContName1,
> Forms!FrmChgCon!ContName2, "Title - Header Description", "do something",
> True
>
>

Does this work?

Dim strSendTo as String
strSendTo = (Trim(Forms!FrmChgCon!ContName) + ",") &
(Trim(Forms!FrmChgCon!ContName1) + ",") &
Trim(Forms!FrmChgCon!ContName2)

If Right(strSendTo, 1) = "," Then
strSendTo = Left(strSendTo, Len(strSendTo) - 1)
End If

stDocName = "RptChangeControlMain"
strReportFilter = "ID = " & Forms!FrmChgCon!ID

DoCmd.OpenReport stDocName, acViewNormal, , strReportFilter

DoCmd.SendObject acSendReport, stDocName, "Snapshot Format",
strSendTo, "Title - Header Description", "do something",
True

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[Database Administrator]

Works perfect Fred - many thanks

John Taylor

[Database Administrator]

Works perfect Fred - many thanks

John Taylor