Email code creating duplicate emails

  • Thread starter Thread starter SAP2
  • Start date Start date
S

SAP2

Hello,
I am working in Access 2003. I am using the following code to send out an
email:

Function SendOutlookMessage( _
strEmailAddress As String, _
strEmailCCAddress As String, _
strEmailBccAddress As String, _
strSubject As String, _
strMessage As String, _
blnDisplayMessage As Boolean, _
Optional strAttachmentFullPath As String)

Dim objApp As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecipient As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim blnOutlookInitiallyOpen As Boolean
Dim strProcName As String

On Error Resume Next
strProcName = "SendOutlookMessage"

blnOutlookInitiallyOpen = True
Set objApp = GetObject(, "Outlook.Application")
If objApp Is Nothing Then
Set objApp = CreateObject("Outlook.Application")
'* Outlook wasn't open when this function started.
blnOutlookInitiallyOpen = False
End If
If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (1): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section

'Create the message
Set objOutlookMsg = objApp.CreateItem(olMailItem)
If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (2): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section

With objOutlookMsg
Set objOutlookRecipient = .Recipients.Add(strEmailAddress)
objOutlookRecipient.Type = olTo
If strEmailCCAddress = "" Then
Else
Set objOutlookRecipient = .Recipients.Add(strEmailCCAddress)
objOutlookRecipient.Type = olCC
End If
If strEmailBccAddress = "" Then
Else
Set objOutlookRecipient = .Recipients.Add(strEmailBccAddress)
objOutlookRecipient.Type = olBCC
End If
.subject = strSubject
.Body = strMessage

'* Add attachments
If Not IsMissing(strAttachmentFullPath) Then
If Trim(strAttachmentFullPath) = "" Then
Else
Set objOutlookAttach = .Attachments.Add(strAttachmentFullPath)
If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (3): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section
End If
End If

If blnDisplayMessage Then
.Display
Else
'* Send message by putting it in the Outbox
.Send
End If
End With

If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (99): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section

Exit_Section:
On Error Resume Next
If Not blnOutlookInitiallyOpen Then
objApp.Quit
End If
Set objApp = Nothing
Set objOutlookMsg = Nothing
Set objOutlookAttach = Nothing
Set objOutlookRecipient = Nothing
On Error GoTo 0
End Function

I call out the function in a query and need to change who the email is sent
to depending on where in the work flow the user is at. When I put the field
[qry4]!(e-mail address removed)
 
SAP2,

Sounds like the qry4 is returning two records... Run the query with that
eMail on the criteria line and see how many records it returns.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

SAP2 said:
Hello,
I am working in Access 2003. I am using the following code to send out an
email:

Function SendOutlookMessage( _
strEmailAddress As String, _
strEmailCCAddress As String, _
strEmailBccAddress As String, _
strSubject As String, _
strMessage As String, _
blnDisplayMessage As Boolean, _
Optional strAttachmentFullPath As String)

Dim objApp As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecipient As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim blnOutlookInitiallyOpen As Boolean
Dim strProcName As String

On Error Resume Next
strProcName = "SendOutlookMessage"

blnOutlookInitiallyOpen = True
Set objApp = GetObject(, "Outlook.Application")
If objApp Is Nothing Then
Set objApp = CreateObject("Outlook.Application")
'* Outlook wasn't open when this function started.
blnOutlookInitiallyOpen = False
End If
If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (1): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section

'Create the message
Set objOutlookMsg = objApp.CreateItem(olMailItem)
If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (2): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section

With objOutlookMsg
Set objOutlookRecipient = .Recipients.Add(strEmailAddress)
objOutlookRecipient.Type = olTo
If strEmailCCAddress = "" Then
Else
Set objOutlookRecipient = .Recipients.Add(strEmailCCAddress)
objOutlookRecipient.Type = olCC
End If
If strEmailBccAddress = "" Then
Else
Set objOutlookRecipient = .Recipients.Add(strEmailBccAddress)
objOutlookRecipient.Type = olBCC
End If
.subject = strSubject
.Body = strMessage

'* Add attachments
If Not IsMissing(strAttachmentFullPath) Then
If Trim(strAttachmentFullPath) = "" Then
Else
Set objOutlookAttach = .Attachments.Add(strAttachmentFullPath)
If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (3): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section
End If
End If

If blnDisplayMessage Then
.Display
Else
'* Send message by putting it in the Outbox
.Send
End If
End With

If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (99): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section

Exit_Section:
On Error Resume Next
If Not blnOutlookInitiallyOpen Then
objApp.Quit
End If
Set objApp = Nothing
Set objOutlookMsg = Nothing
Set objOutlookAttach = Nothing
Set objOutlookRecipient = Nothing
On Error GoTo 0
End Function

I call out the function in a query and need to change who the email is
sent
to depending on where in the work flow the user is at. When I put the
field
[qry4]!(e-mail address removed)
Click to expand...
 
Gina,
The query in datasheet view does not show any values just one blank field,
but it is returning 2 value because they both show up in my outbox (to be
sent).

What is the difference when I use qry4 to input a dynamic email address
(changes with other inputs) and when I type in an email address into the
function in the query? Aren't they both strings? The query (or the code)
acts different between the two scenarios.

Thanks,
SAP2

Gina Whipp said:
SAP2,

Sounds like the qry4 is returning two records... Run the query with that
eMail on the criteria line and see how many records it returns.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

SAP2 said:
Hello,
I am working in Access 2003. I am using the following code to send out an
email:

Function SendOutlookMessage( _
strEmailAddress As String, _
strEmailCCAddress As String, _
strEmailBccAddress As String, _
strSubject As String, _
strMessage As String, _
blnDisplayMessage As Boolean, _
Optional strAttachmentFullPath As String)

Dim objApp As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecipient As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim blnOutlookInitiallyOpen As Boolean
Dim strProcName As String

On Error Resume Next
strProcName = "SendOutlookMessage"

blnOutlookInitiallyOpen = True
Set objApp = GetObject(, "Outlook.Application")
If objApp Is Nothing Then
Set objApp = CreateObject("Outlook.Application")
'* Outlook wasn't open when this function started.
blnOutlookInitiallyOpen = False
End If
If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (1): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section

'Create the message
Set objOutlookMsg = objApp.CreateItem(olMailItem)
If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (2): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section

With objOutlookMsg
Set objOutlookRecipient = .Recipients.Add(strEmailAddress)
objOutlookRecipient.Type = olTo
If strEmailCCAddress = "" Then
Else
Set objOutlookRecipient = .Recipients.Add(strEmailCCAddress)
objOutlookRecipient.Type = olCC
End If
If strEmailBccAddress = "" Then
Else
Set objOutlookRecipient = .Recipients.Add(strEmailBccAddress)
objOutlookRecipient.Type = olBCC
End If
.subject = strSubject
.Body = strMessage

'* Add attachments
If Not IsMissing(strAttachmentFullPath) Then
If Trim(strAttachmentFullPath) = "" Then
Else
Set objOutlookAttach = .Attachments.Add(strAttachmentFullPath)
If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (3): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section
End If
End If

If blnDisplayMessage Then
.Display
Else
'* Send message by putting it in the Outbox
.Send
End If
End With

If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (99): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section

Exit_Section:
On Error Resume Next
If Not blnOutlookInitiallyOpen Then
objApp.Quit
End If
Set objApp = Nothing
Set objOutlookMsg = Nothing
Set objOutlookAttach = Nothing
Set objOutlookRecipient = Nothing
On Error GoTo 0
End Function

I call out the function in a query and need to change who the email is
sent
to depending on where in the work flow the user is at. When I put the
field
[qry4]!(e-mail address removed)
Click to expand...
Click to expand...
 
SAP2,

Okay, that was a contradiction... You said the query in datasheet view
shows no records but when you use it in your function 2 emails are sent to
your Outbox. How can it send any eMails if it returns no records? Did you
*hard code* the eMail address to see what happens? (And, yes they are both
strings. There is no difference, except that you say 1 way returns no
records and the other way returns two records.)

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

SAP2 said:
Gina,
The query in datasheet view does not show any values just one blank field,
but it is returning 2 value because they both show up in my outbox (to be
sent).

What is the difference when I use qry4 to input a dynamic email address
(changes with other inputs) and when I type in an email address into the
function in the query? Aren't they both strings? The query (or the code)
acts different between the two scenarios.

Thanks,
SAP2

Gina Whipp said:
SAP2,

Sounds like the qry4 is returning two records... Run the query with that
eMail on the criteria line and see how many records it returns.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

SAP2 said:
Hello,
I am working in Access 2003. I am using the following code to send out
an
email:

Function SendOutlookMessage( _
strEmailAddress As String, _
strEmailCCAddress As String, _
strEmailBccAddress As String, _
strSubject As String, _
strMessage As String, _
blnDisplayMessage As Boolean, _
Optional strAttachmentFullPath As String)

Dim objApp As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecipient As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim blnOutlookInitiallyOpen As Boolean
Dim strProcName As String

On Error Resume Next
strProcName = "SendOutlookMessage"

blnOutlookInitiallyOpen = True
Set objApp = GetObject(, "Outlook.Application")
If objApp Is Nothing Then
Set objApp = CreateObject("Outlook.Application")
'* Outlook wasn't open when this function started.
blnOutlookInitiallyOpen = False
End If
If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (1): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section

'Create the message
Set objOutlookMsg = objApp.CreateItem(olMailItem)
If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (2): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section

With objOutlookMsg
Set objOutlookRecipient = .Recipients.Add(strEmailAddress)
objOutlookRecipient.Type = olTo
If strEmailCCAddress = "" Then
Else
Set objOutlookRecipient = .Recipients.Add(strEmailCCAddress)
objOutlookRecipient.Type = olCC
End If
If strEmailBccAddress = "" Then
Else
Set objOutlookRecipient = .Recipients.Add(strEmailBccAddress)
objOutlookRecipient.Type = olBCC
End If
.subject = strSubject
.Body = strMessage

'* Add attachments
If Not IsMissing(strAttachmentFullPath) Then
If Trim(strAttachmentFullPath) = "" Then
Else
Set objOutlookAttach =
.Attachments.Add(strAttachmentFullPath)
If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (3): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section
End If
End If

If blnDisplayMessage Then
.Display
Else
'* Send message by putting it in the Outbox
.Send
End If
End With

If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (99): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section

Exit_Section:
On Error Resume Next
If Not blnOutlookInitiallyOpen Then
objApp.Quit
End If
Set objApp = Nothing
Set objOutlookMsg = Nothing
Set objOutlookAttach = Nothing
Set objOutlookRecipient = Nothing
On Error GoTo 0
End Function

I call out the function in a query and need to change who the email is
sent
to depending on where in the work flow the user is at. When I put the
field
[qry4]!(e-mail address removed)
Click to expand...
Click to expand...
Click to expand...
 
Gina,
I know it sounds crazy.

Here is the SQL that results in 1 email in my outlook outbox queue:

SELECT SendOutlookMessage("(e-mail address removed)","","","Test","test",False,"") AS Expr1
FROM Query4
GROUP BY SendOutlookMessage("(e-mail address removed)","","","Test","test",False,"");

And the SQL that results in 2:

SELECT SendOutlookMessage(Query4!Email,"","","Test","test",False,"") AS Expr1
FROM Query4
GROUP BY SendOutlookMessage(Query4!Email,"","","Test","test",False,"");

When I run Query4 by itself it only contains one record that is an email
address.


Gina Whipp said:
SAP2,

Okay, that was a contradiction... You said the query in datasheet view
shows no records but when you use it in your function 2 emails are sent to
your Outbox. How can it send any eMails if it returns no records? Did you
*hard code* the eMail address to see what happens? (And, yes they are both
strings. There is no difference, except that you say 1 way returns no
records and the other way returns two records.)

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

SAP2 said:
Gina,
The query in datasheet view does not show any values just one blank field,
but it is returning 2 value because they both show up in my outbox (to be
sent).

What is the difference when I use qry4 to input a dynamic email address
(changes with other inputs) and when I type in an email address into the
function in the query? Aren't they both strings? The query (or the code)
acts different between the two scenarios.

Thanks,
SAP2

Gina Whipp said:
SAP2,

Sounds like the qry4 is returning two records... Run the query with that
eMail on the criteria line and see how many records it returns.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Hello,
I am working in Access 2003. I am using the following code to send out
an
email:

Function SendOutlookMessage( _
strEmailAddress As String, _
strEmailCCAddress As String, _
strEmailBccAddress As String, _
strSubject As String, _
strMessage As String, _
blnDisplayMessage As Boolean, _
Optional strAttachmentFullPath As String)

Dim objApp As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecipient As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim blnOutlookInitiallyOpen As Boolean
Dim strProcName As String

On Error Resume Next
strProcName = "SendOutlookMessage"

blnOutlookInitiallyOpen = True
Set objApp = GetObject(, "Outlook.Application")
If objApp Is Nothing Then
Set objApp = CreateObject("Outlook.Application")
'* Outlook wasn't open when this function started.
blnOutlookInitiallyOpen = False
End If
If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (1): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section

'Create the message
Set objOutlookMsg = objApp.CreateItem(olMailItem)
If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (2): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section

With objOutlookMsg
Set objOutlookRecipient = .Recipients.Add(strEmailAddress)
objOutlookRecipient.Type = olTo
If strEmailCCAddress = "" Then
Else
Set objOutlookRecipient = .Recipients.Add(strEmailCCAddress)
objOutlookRecipient.Type = olCC
End If
If strEmailBccAddress = "" Then
Else
Set objOutlookRecipient = .Recipients.Add(strEmailBccAddress)
objOutlookRecipient.Type = olBCC
End If
.subject = strSubject
.Body = strMessage

'* Add attachments
If Not IsMissing(strAttachmentFullPath) Then
If Trim(strAttachmentFullPath) = "" Then
Else
Set objOutlookAttach =
.Attachments.Add(strAttachmentFullPath)
If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (3): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section
End If
End If

If blnDisplayMessage Then
.Display
Else
'* Send message by putting it in the Outbox
.Send
End If
End With

If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (99): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section

Exit_Section:
On Error Resume Next
If Not blnOutlookInitiallyOpen Then
objApp.Quit
End If
Set objApp = Nothing
Set objOutlookMsg = Nothing
Set objOutlookAttach = Nothing
Set objOutlookRecipient = Nothing
On Error GoTo 0
End Function

I call out the function in a query and need to change who the email is
sent
to depending on where in the work flow the user is at. When I put the
field
[qry4]!(e-mail address removed)
Click to expand...
Click to expand...
Click to expand...
 
SAP2,

Post the SQL of the query... To do that, open the query in Design View, in
the upper left hand corner change the drop to 'SQL', then copy/paste that
code here.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

SAP2 said:
Gina,
I know it sounds crazy.

Here is the SQL that results in 1 email in my outlook outbox queue:

SELECT SendOutlookMessage("(e-mail address removed)","","","Test","test",False,"") AS
Expr1
FROM Query4
GROUP BY SendOutlookMessage("(e-mail address removed)","","","Test","test",False,"");

And the SQL that results in 2:

SELECT SendOutlookMessage(Query4!Email,"","","Test","test",False,"") AS
Expr1
FROM Query4
GROUP BY SendOutlookMessage(Query4!Email,"","","Test","test",False,"");

When I run Query4 by itself it only contains one record that is an email
address.


Gina Whipp said:
SAP2,

Okay, that was a contradiction... You said the query in datasheet view
shows no records but when you use it in your function 2 emails are sent
to
your Outbox. How can it send any eMails if it returns no records? Did
you
*hard code* the eMail address to see what happens? (And, yes they are
both
strings. There is no difference, except that you say 1 way returns no
records and the other way returns two records.)

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

SAP2 said:
Gina,
The query in datasheet view does not show any values just one blank
field,
but it is returning 2 value because they both show up in my outbox (to
be
sent).

What is the difference when I use qry4 to input a dynamic email address
(changes with other inputs) and when I type in an email address into
the
function in the query? Aren't they both strings? The query (or the
code)
acts different between the two scenarios.

Thanks,
SAP2

:

SAP2,

Sounds like the qry4 is returning two records... Run the query with
that
eMail on the criteria line and see how many records it returns.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Hello,
I am working in Access 2003. I am using the following code to send
out
an
email:

Function SendOutlookMessage( _
strEmailAddress As String, _
strEmailCCAddress As String, _
strEmailBccAddress As String, _
strSubject As String, _
strMessage As String, _
blnDisplayMessage As Boolean, _
Optional strAttachmentFullPath As String)

Dim objApp As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecipient As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim blnOutlookInitiallyOpen As Boolean
Dim strProcName As String

On Error Resume Next
strProcName = "SendOutlookMessage"

blnOutlookInitiallyOpen = True
Set objApp = GetObject(, "Outlook.Application")
If objApp Is Nothing Then
Set objApp = CreateObject("Outlook.Application")
'* Outlook wasn't open when this function started.
blnOutlookInitiallyOpen = False
End If
If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (1): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section

'Create the message
Set objOutlookMsg = objApp.CreateItem(olMailItem)
If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (2): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section

With objOutlookMsg
Set objOutlookRecipient = .Recipients.Add(strEmailAddress)
objOutlookRecipient.Type = olTo
If strEmailCCAddress = "" Then
Else
Set objOutlookRecipient = .Recipients.Add(strEmailCCAddress)
objOutlookRecipient.Type = olCC
End If
If strEmailBccAddress = "" Then
Else
Set objOutlookRecipient = .Recipients.Add(strEmailBccAddress)
objOutlookRecipient.Type = olBCC
End If
.subject = strSubject
.Body = strMessage

'* Add attachments
If Not IsMissing(strAttachmentFullPath) Then
If Trim(strAttachmentFullPath) = "" Then
Else
Set objOutlookAttach =
.Attachments.Add(strAttachmentFullPath)
If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (3): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section
End If
End If

If blnDisplayMessage Then
.Display
Else
'* Send message by putting it in the Outbox
.Send
End If
End With

If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (99): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section

Exit_Section:
On Error Resume Next
If Not blnOutlookInitiallyOpen Then
objApp.Quit
End If
Set objApp = Nothing
Set objOutlookMsg = Nothing
Set objOutlookAttach = Nothing
Set objOutlookRecipient = Nothing
On Error GoTo 0
End Function

I call out the function in a query and need to change who the email
is
sent
to depending on where in the work flow the user is at. When I put
the
field
[qry4]!(e-mail address removed)
Click to expand...
Click to expand...
Click to expand...
 
Gina,
I posted the SQL from the query in my previous post. It shows both scenarios.

Gina Whipp said:
SAP2,

Post the SQL of the query... To do that, open the query in Design View, in
the upper left hand corner change the drop to 'SQL', then copy/paste that
code here.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

SAP2 said:
Gina,
I know it sounds crazy.

Here is the SQL that results in 1 email in my outlook outbox queue:

SELECT SendOutlookMessage("(e-mail address removed)","","","Test","test",False,"") AS
Expr1
FROM Query4
GROUP BY SendOutlookMessage("(e-mail address removed)","","","Test","test",False,"");

And the SQL that results in 2:

SELECT SendOutlookMessage(Query4!Email,"","","Test","test",False,"") AS
Expr1
FROM Query4
GROUP BY SendOutlookMessage(Query4!Email,"","","Test","test",False,"");

When I run Query4 by itself it only contains one record that is an email
address.


Gina Whipp said:
SAP2,

Okay, that was a contradiction... You said the query in datasheet view
shows no records but when you use it in your function 2 emails are sent
to
your Outbox. How can it send any eMails if it returns no records? Did
you
*hard code* the eMail address to see what happens? (And, yes they are
both
strings. There is no difference, except that you say 1 way returns no
records and the other way returns two records.)

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Gina,
The query in datasheet view does not show any values just one blank
field,
but it is returning 2 value because they both show up in my outbox (to
be
sent).

What is the difference when I use qry4 to input a dynamic email address
(changes with other inputs) and when I type in an email address into
the
function in the query? Aren't they both strings? The query (or the
code)
acts different between the two scenarios.

Thanks,
SAP2

:

SAP2,

Sounds like the qry4 is returning two records... Run the query with
that
eMail on the criteria line and see how many records it returns.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Hello,
I am working in Access 2003. I am using the following code to send
out
an
email:

Function SendOutlookMessage( _
strEmailAddress As String, _
strEmailCCAddress As String, _
strEmailBccAddress As String, _
strSubject As String, _
strMessage As String, _
blnDisplayMessage As Boolean, _
Optional strAttachmentFullPath As String)

Dim objApp As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecipient As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim blnOutlookInitiallyOpen As Boolean
Dim strProcName As String

On Error Resume Next
strProcName = "SendOutlookMessage"

blnOutlookInitiallyOpen = True
Set objApp = GetObject(, "Outlook.Application")
If objApp Is Nothing Then
Set objApp = CreateObject("Outlook.Application")
'* Outlook wasn't open when this function started.
blnOutlookInitiallyOpen = False
End If
If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (1): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section

'Create the message
Set objOutlookMsg = objApp.CreateItem(olMailItem)
If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (2): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section

With objOutlookMsg
Set objOutlookRecipient = .Recipients.Add(strEmailAddress)
objOutlookRecipient.Type = olTo
If strEmailCCAddress = "" Then
Else
Set objOutlookRecipient = .Recipients.Add(strEmailCCAddress)
objOutlookRecipient.Type = olCC
End If
If strEmailBccAddress = "" Then
Else
Set objOutlookRecipient = .Recipients.Add(strEmailBccAddress)
objOutlookRecipient.Type = olBCC
End If
.subject = strSubject
.Body = strMessage

'* Add attachments
If Not IsMissing(strAttachmentFullPath) Then
If Trim(strAttachmentFullPath) = "" Then
Else
Set objOutlookAttach =
.Attachments.Add(strAttachmentFullPath)
If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (3): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section
End If
End If

If blnDisplayMessage Then
.Display
Else
'* Send message by putting it in the Outbox
.Send
End If
End With

If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (99): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section

Exit_Section:
On Error Resume Next
If Not blnOutlookInitiallyOpen Then
objApp.Quit
End If
Set objApp = Nothing
Set objOutlookMsg = Nothing
Set objOutlookAttach = Nothing
Set objOutlookRecipient = Nothing
On Error GoTo 0
End Function

I call out the function in a query and need to change who the email
is
sent
to depending on where in the work flow the user is at. When I put
the
field
[qry4]!(e-mail address removed)
Click to expand...
Click to expand...
Click to expand...
 
SAP2,

No, you posted Query4!Email... what is the SQL of Query4?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

SAP2 said:
Gina,
I posted the SQL from the query in my previous post. It shows both
scenarios.

Gina Whipp said:
SAP2,

Post the SQL of the query... To do that, open the query in Design View,
in
the upper left hand corner change the drop to 'SQL', then copy/paste that
code here.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

SAP2 said:
Gina,
I know it sounds crazy.

Here is the SQL that results in 1 email in my outlook outbox queue:

SELECT SendOutlookMessage("(e-mail address removed)","","","Test","test",False,"")
AS
Expr1
FROM Query4
GROUP BY
SendOutlookMessage("(e-mail address removed)","","","Test","test",False,"");

And the SQL that results in 2:

SELECT SendOutlookMessage(Query4!Email,"","","Test","test",False,"") AS
Expr1
FROM Query4
GROUP BY SendOutlookMessage(Query4!Email,"","","Test","test",False,"");

When I run Query4 by itself it only contains one record that is an
email
address.


:

SAP2,

Okay, that was a contradiction... You said the query in datasheet
view
shows no records but when you use it in your function 2 emails are
sent
to
your Outbox. How can it send any eMails if it returns no records?
Did
you
*hard code* the eMail address to see what happens? (And, yes they are
both
strings. There is no difference, except that you say 1 way returns no
records and the other way returns two records.)

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Gina,
The query in datasheet view does not show any values just one blank
field,
but it is returning 2 value because they both show up in my outbox
(to
be
sent).

What is the difference when I use qry4 to input a dynamic email
address
(changes with other inputs) and when I type in an email address into
the
function in the query? Aren't they both strings? The query (or the
code)
acts different between the two scenarios.

Thanks,
SAP2

:

SAP2,

Sounds like the qry4 is returning two records... Run the query with
that
eMail on the criteria line and see how many records it returns.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Hello,
I am working in Access 2003. I am using the following code to
send
out
an
email:

Function SendOutlookMessage( _
strEmailAddress As String, _
strEmailCCAddress As String, _
strEmailBccAddress As String, _
strSubject As String, _
strMessage As String, _
blnDisplayMessage As Boolean, _
Optional strAttachmentFullPath As String)

Dim objApp As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecipient As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim blnOutlookInitiallyOpen As Boolean
Dim strProcName As String

On Error Resume Next
strProcName = "SendOutlookMessage"

blnOutlookInitiallyOpen = True
Set objApp = GetObject(, "Outlook.Application")
If objApp Is Nothing Then
Set objApp = CreateObject("Outlook.Application")
'* Outlook wasn't open when this function started.
blnOutlookInitiallyOpen = False
End If
If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (1): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section

'Create the message
Set objOutlookMsg = objApp.CreateItem(olMailItem)
If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (2): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section

With objOutlookMsg
Set objOutlookRecipient = .Recipients.Add(strEmailAddress)
objOutlookRecipient.Type = olTo
If strEmailCCAddress = "" Then
Else
Set objOutlookRecipient =
.Recipients.Add(strEmailCCAddress)
objOutlookRecipient.Type = olCC
End If
If strEmailBccAddress = "" Then
Else
Set objOutlookRecipient =
.Recipients.Add(strEmailBccAddress)
objOutlookRecipient.Type = olBCC
End If
.subject = strSubject
.Body = strMessage

'* Add attachments
If Not IsMissing(strAttachmentFullPath) Then
If Trim(strAttachmentFullPath) = "" Then
Else
Set objOutlookAttach =
.Attachments.Add(strAttachmentFullPath)
If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (3): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section
End If
End If

If blnDisplayMessage Then
.Display
Else
'* Send message by putting it in the Outbox
.Send
End If
End With

If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (99): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section

Exit_Section:
On Error Resume Next
If Not blnOutlookInitiallyOpen Then
objApp.Quit
End If
Set objApp = Nothing
Set objOutlookMsg = Nothing
Set objOutlookAttach = Nothing
Set objOutlookRecipient = Nothing
On Error GoTo 0
End Function

I call out the function in a query and need to change who the
email
is
sent
to depending on where in the work flow the user is at. When I
put
the
field
[qry4]!(e-mail address removed)
Click to expand...
Click to expand...
Click to expand...
 
Just a thought.
SendOutlookMessage is a function used in the query in both the select and
group by statements.
Won't Access evaluate it twice, and therefore send two emails?

If Query 4 only returns unique email addresses, you don't need the Group By
clause in your final query.

--

AG
Email: npATadhdataDOTcom


Gina Whipp said:
SAP2,

Post the SQL of the query... To do that, open the query in Design View,
in the upper left hand corner change the drop to 'SQL', then copy/paste
that code here.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

SAP2 said:
Gina,
I know it sounds crazy.

Here is the SQL that results in 1 email in my outlook outbox queue:

SELECT SendOutlookMessage("(e-mail address removed)","","","Test","test",False,"") AS
Expr1
FROM Query4
GROUP BY SendOutlookMessage("(e-mail address removed)","","","Test","test",False,"");

And the SQL that results in 2:

SELECT SendOutlookMessage(Query4!Email,"","","Test","test",False,"") AS
Expr1
FROM Query4
GROUP BY SendOutlookMessage(Query4!Email,"","","Test","test",False,"");

When I run Query4 by itself it only contains one record that is an email
address.


Gina Whipp said:
SAP2,

Okay, that was a contradiction... You said the query in datasheet view
shows no records but when you use it in your function 2 emails are sent
to
your Outbox. How can it send any eMails if it returns no records? Did
you
*hard code* the eMail address to see what happens? (And, yes they are
both
strings. There is no difference, except that you say 1 way returns no
records and the other way returns two records.)

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Gina,
The query in datasheet view does not show any values just one blank
field,
but it is returning 2 value because they both show up in my outbox (to
be
sent).

What is the difference when I use qry4 to input a dynamic email
address
(changes with other inputs) and when I type in an email address into
the
function in the query? Aren't they both strings? The query (or the
code)
acts different between the two scenarios.

Thanks,
SAP2

:

SAP2,

Sounds like the qry4 is returning two records... Run the query with
that
eMail on the criteria line and see how many records it returns.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Hello,
I am working in Access 2003. I am using the following code to send
out
an
email:

Function SendOutlookMessage( _
strEmailAddress As String, _
strEmailCCAddress As String, _
strEmailBccAddress As String, _
strSubject As String, _
strMessage As String, _
blnDisplayMessage As Boolean, _
Optional strAttachmentFullPath As String)

Dim objApp As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecipient As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim blnOutlookInitiallyOpen As Boolean
Dim strProcName As String

On Error Resume Next
strProcName = "SendOutlookMessage"

blnOutlookInitiallyOpen = True
Set objApp = GetObject(, "Outlook.Application")
If objApp Is Nothing Then
Set objApp = CreateObject("Outlook.Application")
'* Outlook wasn't open when this function started.
blnOutlookInitiallyOpen = False
End If
If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (1): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section

'Create the message
Set objOutlookMsg = objApp.CreateItem(olMailItem)
If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (2): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section

With objOutlookMsg
Set objOutlookRecipient = .Recipients.Add(strEmailAddress)
objOutlookRecipient.Type = olTo
If strEmailCCAddress = "" Then
Else
Set objOutlookRecipient = .Recipients.Add(strEmailCCAddress)
objOutlookRecipient.Type = olCC
End If
If strEmailBccAddress = "" Then
Else
Set objOutlookRecipient =
.Recipients.Add(strEmailBccAddress)
objOutlookRecipient.Type = olBCC
End If
.subject = strSubject
.Body = strMessage

'* Add attachments
If Not IsMissing(strAttachmentFullPath) Then
If Trim(strAttachmentFullPath) = "" Then
Else
Set objOutlookAttach =
.Attachments.Add(strAttachmentFullPath)
If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (3): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section
End If
End If

If blnDisplayMessage Then
.Display
Else
'* Send message by putting it in the Outbox
.Send
End If
End With

If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (99): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section

Exit_Section:
On Error Resume Next
If Not blnOutlookInitiallyOpen Then
objApp.Quit
End If
Set objApp = Nothing
Set objOutlookMsg = Nothing
Set objOutlookAttach = Nothing
Set objOutlookRecipient = Nothing
On Error GoTo 0
End Function

I call out the function in a query and need to change who the email
is
sent
to depending on where in the work flow the user is at. When I put
the
field
[qry4]!(e-mail address removed)
Click to expand...
Click to expand...
Click to expand...
 
Hey AG,
Good thought. I removed the Group By and it worked like a charm.

I thought I had tried that but I guess I spent too much time looking and
missed the simple explanation.

Thanks.

AG said:
Just a thought.
SendOutlookMessage is a function used in the query in both the select and
group by statements.
Won't Access evaluate it twice, and therefore send two emails?

If Query 4 only returns unique email addresses, you don't need the Group By
clause in your final query.

--

AG
Email: npATadhdataDOTcom


Gina Whipp said:
SAP2,

Post the SQL of the query... To do that, open the query in Design View,
in the upper left hand corner change the drop to 'SQL', then copy/paste
that code here.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

SAP2 said:
Gina,
I know it sounds crazy.

Here is the SQL that results in 1 email in my outlook outbox queue:

SELECT SendOutlookMessage("(e-mail address removed)","","","Test","test",False,"") AS
Expr1
FROM Query4
GROUP BY SendOutlookMessage("(e-mail address removed)","","","Test","test",False,"");

And the SQL that results in 2:

SELECT SendOutlookMessage(Query4!Email,"","","Test","test",False,"") AS
Expr1
FROM Query4
GROUP BY SendOutlookMessage(Query4!Email,"","","Test","test",False,"");

When I run Query4 by itself it only contains one record that is an email
address.


:

SAP2,

Okay, that was a contradiction... You said the query in datasheet view
shows no records but when you use it in your function 2 emails are sent
to
your Outbox. How can it send any eMails if it returns no records? Did
you
*hard code* the eMail address to see what happens? (And, yes they are
both
strings. There is no difference, except that you say 1 way returns no
records and the other way returns two records.)

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Gina,
The query in datasheet view does not show any values just one blank
field,
but it is returning 2 value because they both show up in my outbox (to
be
sent).

What is the difference when I use qry4 to input a dynamic email
address
(changes with other inputs) and when I type in an email address into
the
function in the query? Aren't they both strings? The query (or the
code)
acts different between the two scenarios.

Thanks,
SAP2

:

SAP2,

Sounds like the qry4 is returning two records... Run the query with
that
eMail on the criteria line and see how many records it returns.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Hello,
I am working in Access 2003. I am using the following code to send
out
an
email:

Function SendOutlookMessage( _
strEmailAddress As String, _
strEmailCCAddress As String, _
strEmailBccAddress As String, _
strSubject As String, _
strMessage As String, _
blnDisplayMessage As Boolean, _
Optional strAttachmentFullPath As String)

Dim objApp As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecipient As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim blnOutlookInitiallyOpen As Boolean
Dim strProcName As String

On Error Resume Next
strProcName = "SendOutlookMessage"

blnOutlookInitiallyOpen = True
Set objApp = GetObject(, "Outlook.Application")
If objApp Is Nothing Then
Set objApp = CreateObject("Outlook.Application")
'* Outlook wasn't open when this function started.
blnOutlookInitiallyOpen = False
End If
If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (1): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section

'Create the message
Set objOutlookMsg = objApp.CreateItem(olMailItem)
If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (2): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section

With objOutlookMsg
Set objOutlookRecipient = .Recipients.Add(strEmailAddress)
objOutlookRecipient.Type = olTo
If strEmailCCAddress = "" Then
Else
Set objOutlookRecipient = .Recipients.Add(strEmailCCAddress)
objOutlookRecipient.Type = olCC
End If
If strEmailBccAddress = "" Then
Else
Set objOutlookRecipient =
.Recipients.Add(strEmailBccAddress)
objOutlookRecipient.Type = olBCC
End If
.subject = strSubject
.Body = strMessage

'* Add attachments
If Not IsMissing(strAttachmentFullPath) Then
If Trim(strAttachmentFullPath) = "" Then
Else
Set objOutlookAttach =
.Attachments.Add(strAttachmentFullPath)
If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (3): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section
End If
End If

If blnDisplayMessage Then
.Display
Else
'* Send message by putting it in the Outbox
.Send
End If
End With

If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (99): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section

Exit_Section:
On Error Resume Next
If Not blnOutlookInitiallyOpen Then
objApp.Quit
End If
Set objApp = Nothing
Set objOutlookMsg = Nothing
Set objOutlookAttach = Nothing
Set objOutlookRecipient = Nothing
On Error GoTo 0
End Function

I call out the function in a query and need to change who the email
is
sent
to depending on where in the work flow the user is at. When I put
the
field
[qry4]!(e-mail address removed)
Click to expand...
Click to expand...
Click to expand...
 
Glad to help.

--

AG
Email: npATadhdataDOTcom


SAP2 said:
Hey AG,
Good thought. I removed the Group By and it worked like a charm.

I thought I had tried that but I guess I spent too much time looking and
missed the simple explanation.

Thanks.

AG said:
Just a thought.
SendOutlookMessage is a function used in the query in both the select and
group by statements.
Won't Access evaluate it twice, and therefore send two emails?

If Query 4 only returns unique email addresses, you don't need the Group
By
clause in your final query.

--

AG
Email: npATadhdataDOTcom


Gina Whipp said:
SAP2,

Post the SQL of the query... To do that, open the query in Design
View,
in the upper left hand corner change the drop to 'SQL', then copy/paste
that code here.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Gina,
I know it sounds crazy.

Here is the SQL that results in 1 email in my outlook outbox queue:

SELECT SendOutlookMessage("(e-mail address removed)","","","Test","test",False,"")
AS
Expr1
FROM Query4
GROUP BY
SendOutlookMessage("(e-mail address removed)","","","Test","test",False,"");

And the SQL that results in 2:

SELECT SendOutlookMessage(Query4!Email,"","","Test","test",False,"")
AS
Expr1
FROM Query4
GROUP BY
SendOutlookMessage(Query4!Email,"","","Test","test",False,"");

When I run Query4 by itself it only contains one record that is an
email
address.


:

SAP2,

Okay, that was a contradiction... You said the query in datasheet
view
shows no records but when you use it in your function 2 emails are
sent
to
your Outbox. How can it send any eMails if it returns no records?
Did
you
*hard code* the eMail address to see what happens? (And, yes they
are
both
strings. There is no difference, except that you say 1 way returns
no
records and the other way returns two records.)

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Gina,
The query in datasheet view does not show any values just one blank
field,
but it is returning 2 value because they both show up in my outbox
(to
be
sent).

What is the difference when I use qry4 to input a dynamic email
address
(changes with other inputs) and when I type in an email address
into
the
function in the query? Aren't they both strings? The query (or
the
code)
acts different between the two scenarios.

Thanks,
SAP2

:

SAP2,

Sounds like the qry4 is returning two records... Run the query
with
that
eMail on the criteria line and see how many records it returns.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Hello,
I am working in Access 2003. I am using the following code to
send
out
an
email:

Function SendOutlookMessage( _
strEmailAddress As String, _
strEmailCCAddress As String, _
strEmailBccAddress As String, _
strSubject As String, _
strMessage As String, _
blnDisplayMessage As Boolean, _
Optional strAttachmentFullPath As String)

Dim objApp As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecipient As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim blnOutlookInitiallyOpen As Boolean
Dim strProcName As String

On Error Resume Next
strProcName = "SendOutlookMessage"

blnOutlookInitiallyOpen = True
Set objApp = GetObject(, "Outlook.Application")
If objApp Is Nothing Then
Set objApp = CreateObject("Outlook.Application")
'* Outlook wasn't open when this function started.
blnOutlookInitiallyOpen = False
End If
If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (1): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section

'Create the message
Set objOutlookMsg = objApp.CreateItem(olMailItem)
If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (2): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section

With objOutlookMsg
Set objOutlookRecipient = .Recipients.Add(strEmailAddress)
objOutlookRecipient.Type = olTo
If strEmailCCAddress = "" Then
Else
Set objOutlookRecipient =
.Recipients.Add(strEmailCCAddress)
objOutlookRecipient.Type = olCC
End If
If strEmailBccAddress = "" Then
Else
Set objOutlookRecipient =
.Recipients.Add(strEmailBccAddress)
objOutlookRecipient.Type = olBCC
End If
.subject = strSubject
.Body = strMessage

'* Add attachments
If Not IsMissing(strAttachmentFullPath) Then
If Trim(strAttachmentFullPath) = "" Then
Else
Set objOutlookAttach =
.Attachments.Add(strAttachmentFullPath)
If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (3): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section
End If
End If

If blnDisplayMessage Then
.Display
Else
'* Send message by putting it in the Outbox
.Send
End If
End With

If Err <> 0 Then Beep: _
MsgBox "Error in " & strProcName & " (99): " _
& Err.Number & " - " & Err.Description: _
Err.Clear: _
GoTo Exit_Section

Exit_Section:
On Error Resume Next
If Not blnOutlookInitiallyOpen Then
objApp.Quit
End If
Set objApp = Nothing
Set objOutlookMsg = Nothing
Set objOutlookAttach = Nothing
Set objOutlookRecipient = Nothing
On Error GoTo 0
End Function

I call out the function in a query and need to change who the
email
is
sent
to depending on where in the work flow the user is at. When I
put
the
field
[qry4]!(e-mail address removed)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top