CODEWITHSUNDEEP
emailms-accessvba
Guilherme Brum
Guilherme Brum

Reputation: 51

Syntax to add second email address to cc field

When I click the send e-mail button in MS Access, the following runs: 

EmailDatabaseObject  
To: =DLookUp("[E-mail Address]","Contacts","[ID]=" & Nz([Assigned To],0))  
CC: =IIf(DLookUp("[E-mail Address]","Contacts","[ID]=" & 
     Nz([Opened By],0))=DLookUp("[E-mail Address]","Contacts","[ID]=" &
     Nz([Assigned To],0)),"",DLookUp("[E-mail Address]","Contacts","[ID]=" & Nz([Opened By],0)))

and other items for subject, message and so on.

I would like to add another e-mail address to the CC field.

Upvotes: 0

Views: 258

Answers (3)

Lee Mac
Lee Mac

Reputation: 16015

Since the CC field in your macro currently contains an iif statement, the required modification depends on whether you wish to send the email to your additional email address for all cases, or only for the case in which the iif test expression is validated.

Currently, your iif statement is performing the following test:

=IIf
(
    DLookUp("[E-mail Address]","Contacts","[ID]=" & Nz([Opened By],0))=
    DLookUp("[E-mail Address]","Contacts","[ID]=" & Nz([Assigned To],0)),
    "",
    DLookUp("[E-mail Address]","Contacts","[ID]=" & Nz([Opened By],0))
)

That is to say:

If the contact email address for the Opened By ID is equal to the contact email address for the Assigned To ID, then the CC field is blank (since the To field already contains the Assigned To email address); else use the Opened By email address.

The easiest modification would of course be to simply concatenate the additional email address to the start or end of the iif statement, e.g.:

=IIf
(
    DLookUp("[E-mail Address]","Contacts","[ID]=" & Nz([Opened By],0))=
    DLookUp("[E-mail Address]","Contacts","[ID]=" & Nz([Assigned To],0)),
    "",
    DLookUp("[E-mail Address]","Contacts","[ID]=" & Nz([Opened By],0))
) 
& ";[email protected]"

="[email protected];" &
IIf
(
    DLookUp("[E-mail Address]","Contacts","[ID]=" & Nz([Opened By],0))=
    DLookUp("[E-mail Address]","Contacts","[ID]=" & Nz([Assigned To],0)),
    "",
    DLookUp("[E-mail Address]","Contacts","[ID]=" & Nz([Opened By],0))
)

However, this would yield a leading/trailing semi-colon for the case in which the Opened By email address is equal to the Assigned To email address.

Therefore, to ensure that you are not left with a leading/trailing semi-colon, you may wish to use:

=IIf
(
    DLookUp("[E-mail Address]","Contacts","[ID]=" & Nz([Opened By],0))=
    DLookUp("[E-mail Address]","Contacts","[ID]=" & Nz([Assigned To],0)),
    "[email protected]",
    DLookUp("[E-mail Address]","Contacts","[ID]=" & Nz([Opened By],0)) & ";[email protected]"
)

Upvotes: 3

Santosh
Santosh

Reputation: 12353

CC should be CC: =IIf(DLookup("[E-mail Address]", "Contacts", "[ID]=" & Nz([Opened By], 0)) = DLookup("[E-mail Address]", "Contacts", "[ID]=" & Nz([Assigned To], 0)), "", DLookup("[E-mail Address]", "Contacts", "[ID]=" & Nz([Opened By], 0)) & ";" & "[email protected]")

Upvotes: 1

Sukhi
Sukhi

Reputation: 14185

Just concatenate existing IIF(.....) with the desired email address. Pseudocode :

a = IIF(......)
b = "[email protected]"
c = a + ";" + b
.
.
.
CC = c

Upvotes: 0

Related Questions