By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,401 Members | 928 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,401 IT Pros & Developers. It's quick & easy.

Sending Mail

P: n/a


Hi. I have the following code in a module which sends email via Outlook.

Option Compare Database
Option Explicit

' Declare module level variables
Dim mOutlookApp As Outlook.Application
Dim mNameSpace As Outlook.NameSpace
Dim mFolder As mapiFolder
Dim mItem As MailItem
Dim fSuccess As Boolean

' Module contains only 2 methods:
' 1) GetOutlook()
' 2) SendMessage()
'
Public Function GetOutlook() As Boolean
' The GetOutlook() function sets the Outlook Application
' and Namespase objects and opens MS Outlook
On Error Resume Next

' Assume success
fSuccess = True

Set mOutlookApp = GetObject("", "Outlook.application")

' If Outlook is NOT Open, then there will be an error.
' Attempt to open Outlook
If Err.Number > 0 Then
Err.Clear
Set mOutlookApp = CreateObject("Outlook.application")
If Err.Number > 0 Then
MsgBox "Could not create Outlook object", vbCritical
fSuccess = False
Exit Function
End If
End If

' If we've made it this far, we have an Outlook App Object
' Now, set the NameSpace object to MAPI Namespace
Set mNameSpace = mOutlookApp.GetNamespace("MAPI")

If Err.Number > 0 Then
MsgBox "Could not create NameSpace object", vbCritical
fSuccess = False
Exit Function
End If

' Return the Success Flag as the value of GetOutlook()
GetOutlook = fSuccess
End Function
Public Function SendMessage() As Boolean
' The SendMessage() function reads user entered values and
' actually sends the message.

On Error Resume Next
Dim strRecip As String
Dim strSubject As String
Dim strMsg As String
Dim strAttachment As String
Dim StrBody As String
Dim dlgopenfile As FileDialog

strSubject = Forms!frmsendmail!TxtSubject
strRecip = Forms!frmsendmail!TxtRecipient
StrBody = Forms!frmsendmail!TxtBody
strAttachment = dlgopenfile.SelectedItems(0)
strAttachment = Forms!frmsendmail!TxtAttachment
' Any amount of validation could be done at this point, but
' at a minimum, you need to verify that the user supplied an
' Email address for a recipient.
If Len(strRecip) = 0 Then
strMsg = "You must designate a recipient."
FormattedMsgBox strMsg, vbExclamation, "Error"
Exit Function
ElseIf Len(strSubject) = 0 Then
strMsg = "Your message must have a subject."
FormattedMsgBox strMsg, vbExclamation, "Error"
Forms!frmsendmail!TxtSubject.SetFocus
Exit Function
ElseIf Len(StrBody) = 0 Then
strMsg = "Your message must have some text in the body."
FormattedMsgBox strMsg, vbExclamation, "Error"
Forms!frmsendmail!TxtBody.SetFocus
Exit Function
End If
' Assume success
fSuccess = True
' Here's where the real Outlook Automation takes place
If GetOutlook = True Then
Set mItem = mOutlookApp.CreateItem(olMailItem)
mItem.Recipients.Add strRecip
mItem.Subject = strSubject
mItem.Body = StrBody
mItem.Attachments = strAttachment
' This code allows for 1 attachment, but with slight
' modification, you could provide for multiple files.
If Len(strAttachment) > 0 Then
mItem.Attachments.Add strAttachment
End If
mItem.Send
End If

' Release resources
Set mOutlookApp = Nothing
Set mNameSpace = Nothing

If Err.Number > 0 Then fSuccess = False

SendMessage = fSuccess

End Function
' -- End Code Here -->
This is the way I call the Modules from a command button:

If GetOutlook = False Then
MsgBox "Outlook is closed", vbOKOnly
Call GetOutlook
MsgBox "Outlook is open", vbOKOnly
End If
Call SendMessage
If SendMessage = True Then
FormattedMsgBox "The Message was sent successfully."
End If
DoCmd.Close
End Sub

Everything works with the exception of three problems:

1.) When I click the Send Mail Button on the form, the code sends TWO
identical emails to the address specified.

2.) I cannot figure out how to add attachments that I choose from the
openfile dialog. I can choose files but I do not see them listed in the
txtattachments dialog and they do not get sent with the email.

3.) Iam using Office System Outlook and I get annoying security warnings
when I send the E-Mails. Is there a way to suppress the warnings?

Thank you for your assistance

Colin

P.S.

If you recognize this code, could you please tell me who wrote it so
that I can credit them properly?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #1
Share this Question
Share on Google+
11 Replies


P: n/a
I figured out another method from Microsoft and it solves all my
problems except that I still get two identical email messages going to
the same address. This is happening because the code is looping around
the sendmessage methiod, but I cannot figure out why.

Thanks

Colin

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #2

P: n/a
what was the method??
---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.693 / Virus Database: 454 - Release Date: 31/05/2004
Nov 13 '05 #3

P: n/a
you're calling sendMessage() twice
Call SendMessage
If SendMessage = True Then
FormattedMsgBox "The Message was sent successfully."
End If

change it to
dim blnSent as boolean
blnSent = sendmessage()
if (blnSent) then
FormattedMsgBox "The Message was sent successfully."
End If

ColinWard <je*********@hotmail.com> wrote in message news:<40**********************@news.newsgroups.ws> ...
I figured out another method from Microsoft and it solves all my
problems except that I still get two identical email messages going to
the same address. This is happening because the code is looping around
the sendmessage methiod, but I cannot figure out why.

Thanks

Colin

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 13 '05 #4

P: n/a
That worked perfectly!! Thanks Roger

Colin

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #5

P: n/a

Well it seems jumped the gun a wee bit. Yesyerday I thought I had
figured out my attachment problem but today I was testing and although I
can select files and see them listed on my form, they are not being
attached to my email.
Here is the module that composes and actually sends the E-Mail. Am I
somehow attaching the attachments wrong?
Public Function SendMessage() As Boolean
' The SendMessage() function reads user entered values and
' actually sends the message.

On Error Resume Next
Dim strRecip As String
Dim strSubject As String
Dim strMsg As String
Dim strAttachment As String
Dim StrBody As String
Dim dlgopenfile As FileDialog

strSubject = Forms!frmsendmail!TxtSubject
strRecip = Forms!frmsendmail!TxtRecipient
StrBody = Forms!frmsendmail!TxtBody
strAttachment = Forms!frmsendmail!LstAttachment
' Any amount of validation could be done at this point, but
' at a minimum, you need to verify that the user supplied an
' Email address for a recipient.
If Len(strRecip) = 0 Then
strMsg = "You must designate a recipient."
FormattedMsgBox strMsg, vbExclamation, "Error"
Exit Function
ElseIf Len(strSubject) = 0 Then
strMsg = "Your message must have a subject."
FormattedMsgBox strMsg, vbExclamation, "Error"
Forms!frmsendmail!TxtSubject.SetFocus
Exit Function
ElseIf Len(StrBody) = 0 Then
strMsg = "Your message must have some text in the body."
FormattedMsgBox strMsg, vbExclamation, "Error"
Forms!frmsendmail!TxtBody.SetFocus
Exit Function
End If
' Assume success
fSuccess = True
' Here's where the real Outlook Automation takes place
If GetOutlook = True Then
Set mItem = mOutlookApp.CreateItem(olMailItem)
mItem.Recipients.Add strRecip
mItem.Subject = strSubject
mItem.Body = StrBody
mItem.Attachments.Add (strAttachment)
mItem.Send
End If
' Release resources
Set mOutlookApp = Nothing
Set mNameSpace = Nothing

If Err.Number > 0 Then fSuccess = False

SendMessage = fSuccess

End Function
' -- End Code Here -->

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #6

P: n/a
Hi again.

It appears I am still having problems sending mail. Below is the code
that actually composes the message and sends the email. Everything works
except for the attachments. The attachments are chosen from the openfile
dialog and they display in a listbox on a form. This works, but the
files themselves are not being attached to the mail. strattachment is
always null. I also want to send mutiple attachments.

Public Function SendMessage() As Boolean
' The SendMessage() function reads user entered values and
' actually sends the message.

21090 On Error Resume Next
Dim strRecip As String
Dim strSubject As String
Dim strMsg As String
Dim strAttachment As String
Dim StrBody As String
Dim dlgopenfile As FileDialog
21100 strSubject = Forms!frmsendmail!TxtSubject
21110 strRecip = Forms!frmsendmail!TxtRecipient
21120 StrBody = Forms!frmsendmail!TxtBody
21130 strAttachment = Forms!frmsendmail!TxtAttachments
' Any amount of validation could be done at this point, but
' at a minimum, you need to verify that the user supplied an
' Email address for a recipient.
21140 If Len(strRecip) = 0 Then
21150 strMsg = "You must designate a recipient."
21160 FormattedMsgBox strMsg, vbExclamation, "Error"
21170 Exit Function
21180 ElseIf Len(strSubject) = 0 Then
21190 strMsg = "Your message must have a subject."
21200 FormattedMsgBox strMsg, vbExclamation, "Error"
21210 Forms!frmsendmail!TxtSubject.SetFocus
21220 Exit Function
21230 ElseIf Len(StrBody) = 0 Then
21240 strMsg = "Your message must have some text in the body."
21250 FormattedMsgBox strMsg, vbExclamation, "Error"
21260 Forms!frmsendmail!TxtBody.SetFocus
21270 Exit Function
21280 End If
' Assume success
21290 fSuccess = True
' Here's where the real Outlook Automation takes place
21300 If GetOutlook = True Then
21310 Set mItem = mOutlookApp.CreateItem(olMailItem)
21320 mItem.Recipients.Add strRecip
21330 mItem.Subject = strSubject
21340 mItem.Body = StrBody
21350 mItem.Attachments.AddstrAttachment
21360 mItem.Send
21370 End If
' Release resources
21380 Set mOutlookApp = Nothing
21390 Set mNameSpace = Nothing

21400 If Err.Number > 0 Then fSuccess = False

21410 SendMessage = fSuccess

End Function
' -- End Code Here -->

Thank you

Colin

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #7

P: n/a


Hi again.

Everything works as intended now except that I can only send one
attachment. How would I modify the module so that I could send multiple
files? I have posted the module so that it is easier for you to see what
it is I am trying to do.

--Code Start--
Option Compare Database
Option Explicit

' Declare module level variables
Dim mOutlookApp As Outlook.Application
Dim mNameSpace As Outlook.NameSpace
Dim mFolder As mapiFolder
Dim mItem As MailItem
Dim fSuccess As Boolean
' Module contains only 2 methods:
' 1) GetOutlook()
' 2) SendMessage()
'
Public Function GetOutlook() As Boolean
' The GetOutlook() function sets the Outlook Application
' and Namespase objects and opens MS Outlook
On Error Resume Next

' Assume success
fSuccess = True

Set mOutlookApp = GetObject("", "Outlook.application")

' If Outlook is NOT Open, then there will be an error.
' Attempt to open Outlook
If Err.Number > 0 Then
Err.Clear
Set mOutlookApp = CreateObject("Outlook.application")
If Err.Number > 0 Then
MsgBox "Could not create Outlook object", vbCritical
fSuccess = False
Exit Function
End If
End If

' If we've made it this far, we have an Outlook App Object
' Now, set the NameSpace object to MAPI Namespace
Set mNameSpace = mOutlookApp.GetNamespace("MAPI")

If Err.Number > 0 Then
MsgBox "Could not create NameSpace object", vbCritical
fSuccess = False
Exit Function
End If

' Return the Success Flag as the value of GetOutlook()
GetOutlook = fSuccess
End Function
Public Function SendMessage() As Boolean
' The SendMessage() function reads user entered values and
' actually sends the message.

On Error Resume Next
Dim strRecip As String
Dim strSubject As String
Dim strMsg As String
Dim strAttachment As String
Dim StrBody As String
Dim dlgopenfile As FileDialog
strSubject = Forms!frmsendmail!TxtSubject
strRecip = Forms!frmsendmail!TxtRecipient
StrBody = Forms!frmsendmail!TxtBody
strAttachment = Forms!frmsendmail!TxtAttachment
' Any amount of validation could be done at this point, but
' at a minimum, you need to verify that the user supplied an
' Email address for a recipient.
If Len(strSubject) = 0 Then
strMsg = "Your message must have a subject."
FormattedMsgBox strMsg, vbExclamation, "Error"
Forms!frmsendmail!TxtSubject.SetFocus
Exit Function
ElseIf Len(StrBody) = 0 Then
strMsg = "Your message must have some text in the body."
FormattedMsgBox strMsg, vbExclamation, "Error"
Forms!frmsendmail!TxtBody.SetFocus
Exit Function
End If
' Assume success
fSuccess = True
' Here's where the real Outlook Automation takes place
If GetOutlook = True Then
Set mItem = mOutlookApp.CreateItem(olMailItem)
mItem.Recipients.Add strRecip
mItem.Subject = strSubject
mItem.Body = StrBody
this is what needs to be modified(I think!)--> ' This code allows for
1 attachment, but with slight
' modification, you could provide for multiple files.
If Len(strAttachment) > 0 Then
mItem.Attachments.Add strAttachment
End If
mItem.Send
End If
' Release resources
Set mOutlookApp = Nothing
Set mNameSpace = Nothing

If Err.Number > 0 Then fSuccess = False

SendMessage = fSuccess

End Function
' -- End Code Here -->


thank you

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #8

P: n/a
Modify the lines you specified...

' modification, you could provide for multiple files.
If Len(strAttachment) > 0 Then
mItem.Attachments.Add strAttachment
End If

you could do any number of things...

wrap the section in a Do...Loop and prompt the user for more...

do until intReply=vbno
mItem.Attachments.Add strAttachment
intReply=Msgbox("Add more attachments?",vbyesno)
if intReply = vbyes then
'--prompt user for next file.
'--You could use something like the FileOpen API from the API
section at mvps.org
strAttachment= GetOpenFile()... 'or whatever the function is
called!
loop
Nov 13 '05 #9

P: n/a
Thanks for your advice Pieter. However, it doesn't work properly because
it simply sends multiple copies of the last attachment the user chose.
so the loop is working but it is only looping around the one file.

The way it works now is this:

1) The user clicks the send E-Mail button which opens a form which I
designed which has recipient, subject, body and attachment fields.

2) The recipient email is automatically filled in by the program.

3) the user fills in the subject and body fields which are required.

4) the user can choose to click the Add attachment button or not. If
they click it, then,

5) the windows file browser window opens and the user can
choose the file they want to attach.

6) The user clicks the send mail button. The message is then composed by
the SendMessage function and sent.

sorry if this redundant. I just want to make sure that you understand
how I see the process working.

Thanks pieter

Colin




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #10

P: n/a
ColinWard <je*********@hotmail.com> wrote in message news:<41**********************@news.newsgroups.ws> ...
Thanks for your advice Pieter. However, it doesn't work properly because
it simply sends multiple copies of the last attachment the user chose.
so the loop is working but it is only looping around the one file.

The way it works now is this:

1) The user clicks the send E-Mail button which opens a form which I
designed which has recipient, subject, body and attachment fields.

2) The recipient email is automatically filled in by the program.

3) the user fills in the subject and body fields which are required.

4) the user can choose to click the Add attachment button or not. If
they click it, then,

5) the windows file browser window opens and the user can
choose the file they want to attach.

6) The user clicks the send mail button. The message is then composed by
the SendMessage function and sent.

sorry if this redundant. I just want to make sure that you understand
how I see the process working.

Thanks pieter

Colin


Okay, so the ONLY issue here is how to control what attachments are
added? Braindead it's so easy. Create a variable to hold the name of
the attachment file. use the OpenFile API from the API section of
www.mvps.org. (it's the first article in the API's section). stuff
the return value from the GetOpenFile function into the string
variable. pass that result to the

olMsg.Attachments.Add(strAttachment)

part of your message. Since you're only calling this once or calling
the Add stuff once per attachment, you can just keep adding them.
Does that solve the problem?
Oh, and be sure to destroy all your variables once you're done with
them so you don't have some stupid global attachment variable that's
keeping its value after the routine is run. Make everything as local
as possible.
Nov 13 '05 #11

P: n/a
ColinWard <je*********@hotmail.com> wrote in message news:<41**********************@news.newsgroups.ws> ...
Thanks for your advice Pieter. However, it doesn't work properly because
it simply sends multiple copies of the last attachment the user chose.
so the loop is working but it is only looping around the one file.

The way it works now is this:

1) The user clicks the send E-Mail button which opens a form which I
designed which has recipient, subject, body and attachment fields.

2) The recipient email is automatically filled in by the program.

3) the user fills in the subject and body fields which are required.

4) the user can choose to click the Add attachment button or not. If
they click it, then,

5) the windows file browser window opens and the user can
choose the file they want to attach.

6) The user clicks the send mail button. The message is then composed by
the SendMessage function and sent.

sorry if this redundant. I just want to make sure that you understand
how I see the process working.

Thanks pieter

Colin


Okay, so the ONLY issue here is how to control what attachments are
added? Braindead it's so easy. Create a variable to hold the name of
the attachment file. use the OpenFile API from the API section of
www.mvps.org. (it's the first article in the API's section). stuff
the return value from the GetOpenFile function into the string
variable. pass that result to the

olMsg.Attachments.Add(strAttachment)

part of your message. Since you're only calling this once or calling
the Add stuff once per attachment, you can just keep adding them.
Does that solve the problem?
Oh, and be sure to destroy all your variables once you're done with
them so you don't have some stupid global attachment variable that's
keeping its value after the routine is run. Make everything as local
as possible.
Nov 13 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.