Trying to Send Email, Via Outlook, Using VBA

  • Thread starter Thread starter ryguy7272
  • Start date Start date
R

ryguy7272

I followed the example at this site:
http://support.microsoft.com/kb/318881

It didn't work after I modified the code slightly; trying to use this code:

Sub SendMessages(Optional AttachmentPath)
Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMailingList")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![EmailAddress]

With objOutlookMsg
' Add the To recipients to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo

' Add the Cc recipients to the e-mail message.
If (IsNull(Forms!frmMail!CCAddress)) Then
Else
Set objOutlookRecip = .Recipients.Add(Forms!frmMail!CCAddress)
objOutlookRecip.Type = olCC
End If

' Set the Subject, the Body, and the Importance of the e-mail message.
.Subject = Forms!frmMail!Subject
.Body = Forms!frmMail!MainText
.Importance = olImportanceHigh 'High importance

'Add the attachment to the e-mail message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If

' Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

Private Sub SendMessg_Click()
Call SendMessages
End Sub

I get this message:
"Run Time Error '13'"
Type Mismatch

Error occurs on this line:
Set MyRS = MyDB.OpenRecordset("tblMailingList")


References are set to
Outlook 10.0 Object Library
Microsoft DAO 3.6 Object Library

I have three text files which I wanted to use as attachments. All are
located in this directory:
C:\Documents and Settings\ThinkPad\Desktop\Text Files

I am just trying to find an easy way to send out a few dozen emails, based
on email addresses saved in a table, which I use for work. One thing that I
am doing different is that I am trying to run the code from a Command Button;
no big deal right. I don’t want to go into the DeBug Window and type any
commands. Should be pretty straightforward; just not seeing the answer
here…. Any help would be greatly appreciated!!

Below, I found a great example of sending multiple emails:
http://www.datastrat.com/DataStrat2.html
‘EmailSenate2K’
This is similar to what I want to do, but it uses a ListBox to display all
(names) recipients of the emails. This is pretty clever; I am just trying to
figure out a way to send multiple emails to people in my office, once per
week, to notify them of updates.

Regards,
Ryan---
 
Check this out:
http://www.thepoorhouse.org.uk/run_time_error_13_and_recordsets_in_access
regards
Henk

ryguy7272 skrev:
I followed the example at this site:
http://support.microsoft.com/kb/318881

It didn't work after I modified the code slightly; trying to use this code:

Sub SendMessages(Optional AttachmentPath)
Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMailingList")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![EmailAddress]

With objOutlookMsg
' Add the To recipients to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo

' Add the Cc recipients to the e-mail message.
If (IsNull(Forms!frmMail!CCAddress)) Then
Else
Set objOutlookRecip = .Recipients.Add(Forms!frmMail!CCAddress)
objOutlookRecip.Type = olCC
End If

' Set the Subject, the Body, and the Importance of the e-mail message.
.Subject = Forms!frmMail!Subject
.Body = Forms!frmMail!MainText
.Importance = olImportanceHigh 'High importance

'Add the attachment to the e-mail message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If

' Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

Private Sub SendMessg_Click()
Call SendMessages
End Sub

I get this message:
"Run Time Error '13'"
Type Mismatch

Error occurs on this line:
Set MyRS = MyDB.OpenRecordset("tblMailingList")


References are set to
Outlook 10.0 Object Library
Microsoft DAO 3.6 Object Library

I have three text files which I wanted to use as attachments. All are
located in this directory:
C:\Documents and Settings\ThinkPad\Desktop\Text Files

I am just trying to find an easy way to send out a few dozen emails, based
on email addresses saved in a table, which I use for work. One thing that I
am doing different is that I am trying to run the code from a Command Button;
no big deal right. I don’t want to go into the DeBug Window and type any
commands. Should be pretty straightforward; just not seeing the answer
here…. Any help would be greatly appreciated!!

Below, I found a great example of sending multiple emails:
http://www.datastrat.com/DataStrat2.html
‘EmailSenate2K’
This is similar to what I want to do, but it uses a ListBox to display all
(names) recipients of the emails. This is pretty clever; I am just trying to
figure out a way to send multiple emails to people in my office, once per
week, to notify them of updates.

Regards,
Ryan---
Click to expand...
 
Hummm, never had to do that before, but ok. Now I have these lines in the
module:

Dim myADORecordset As ADODB.Recordset
Dim myDAORecordset As DAO.Recordset


Now, when I run the code I get an error that reads:
'Compile Error: Method or data error not found'

Code fails on this line:
RS.Edit

Foiled for now; hopefully not for long.

Any other suggestions?


Thanks,
Ryan---

--
RyGuy


Henk said:
Check this out:
http://www.thepoorhouse.org.uk/run_time_error_13_and_recordsets_in_access
regards
Henk

ryguy7272 skrev:
I followed the example at this site:
http://support.microsoft.com/kb/318881

It didn't work after I modified the code slightly; trying to use this code:

Sub SendMessages(Optional AttachmentPath)
Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMailingList")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![EmailAddress]

With objOutlookMsg
' Add the To recipients to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo

' Add the Cc recipients to the e-mail message.
If (IsNull(Forms!frmMail!CCAddress)) Then
Else
Set objOutlookRecip = .Recipients.Add(Forms!frmMail!CCAddress)
objOutlookRecip.Type = olCC
End If

' Set the Subject, the Body, and the Importance of the e-mail message.
.Subject = Forms!frmMail!Subject
.Body = Forms!frmMail!MainText
.Importance = olImportanceHigh 'High importance

'Add the attachment to the e-mail message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If

' Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

Private Sub SendMessg_Click()
Call SendMessages
End Sub

I get this message:
"Run Time Error '13'"
Type Mismatch

Error occurs on this line:
Set MyRS = MyDB.OpenRecordset("tblMailingList")


References are set to
Outlook 10.0 Object Library
Microsoft DAO 3.6 Object Library

I have three text files which I wanted to use as attachments. All are
located in this directory:
C:\Documents and Settings\ThinkPad\Desktop\Text Files

I am just trying to find an easy way to send out a few dozen emails, based
on email addresses saved in a table, which I use for work. One thing that I
am doing different is that I am trying to run the code from a Command Button;
no big deal right. I don’t want to go into the DeBug Window and type any
commands. Should be pretty straightforward; just not seeing the answer
here…. Any help would be greatly appreciated!!

Below, I found a great example of sending multiple emails:
http://www.datastrat.com/DataStrat2.html
‘EmailSenate2K’
This is similar to what I want to do, but it uses a ListBox to display all
(names) recipients of the emails. This is pretty clever; I am just trying to
figure out a way to send multiple emails to people in my office, once per
week, to notify them of updates.

Regards,
Ryan---
Click to expand...
Click to expand...
 
Did you actually change the name of the recordset? If so, you need to change
it in the line that fails as well.

If you didn't change the name of the recordset, be aware that there's no
Edit method for ADO recordsets.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


ryguy7272 said:
Hummm, never had to do that before, but ok. Now I have these lines in the
module:

Dim myADORecordset As ADODB.Recordset
Dim myDAORecordset As DAO.Recordset


Now, when I run the code I get an error that reads:
'Compile Error: Method or data error not found'

Code fails on this line:
RS.Edit

Foiled for now; hopefully not for long.

Any other suggestions?


Thanks,
Ryan---

--
RyGuy


Henk said:
Check this out:
http://www.thepoorhouse.org.uk/run_time_error_13_and_recordsets_in_access
regards
Henk

ryguy7272 skrev:
I followed the example at this site:
http://support.microsoft.com/kb/318881

It didn't work after I modified the code slightly; trying to use this
code:

Sub SendMessages(Optional AttachmentPath)
Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMailingList")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![EmailAddress]

With objOutlookMsg
' Add the To recipients to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo

' Add the Cc recipients to the e-mail message.
If (IsNull(Forms!frmMail!CCAddress)) Then
Else
Set objOutlookRecip =
.Recipients.Add(Forms!frmMail!CCAddress)
objOutlookRecip.Type = olCC
End If

' Set the Subject, the Body, and the Importance of the e-mail
message.
.Subject = Forms!frmMail!Subject
.Body = Forms!frmMail!MainText
.Importance = olImportanceHigh 'High importance

'Add the attachment to the e-mail message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If

' Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

Private Sub SendMessg_Click()
Call SendMessages
End Sub

I get this message:
"Run Time Error '13'"
Type Mismatch

Error occurs on this line:
Set MyRS = MyDB.OpenRecordset("tblMailingList")


References are set to
Outlook 10.0 Object Library
Microsoft DAO 3.6 Object Library

I have three text files which I wanted to use as attachments. All are
located in this directory:
C:\Documents and Settings\ThinkPad\Desktop\Text Files

I am just trying to find an easy way to send out a few dozen emails,
based
on email addresses saved in a table, which I use for work. One thing
that I
am doing different is that I am trying to run the code from a Command
Button;
no big deal right. I don't want to go into the DeBug Window and type
any
commands. Should be pretty straightforward; just not seeing the answer
here.. Any help would be greatly appreciated!!

Below, I found a great example of sending multiple emails:
http://www.datastrat.com/DataStrat2.html
'EmailSenate2K'
This is similar to what I want to do, but it uses a ListBox to display
all
(names) recipients of the emails. This is pretty clever; I am just
trying to
figure out a way to send multiple emails to people in my office, once
per
week, to notify them of updates.

Regards,
Ryan---
Click to expand...
Click to expand...
Click to expand...
 
Thanks for the tip Doug. Now, It fails on this line:
Set MyRS = MyDB.OpenRecordset("tblMailingList")

The Table is named as 'tblMailingList'. I don't think that is thge problem,
but when I place the mouse over the error-line, I see the balloon message and
this...'MyRS = Nothing'. Very bizarre! How could it be nothing? I am not
an Access expert by any means, but this seems pretty straightforward...just
can't see the problem here...

I'd welcome any more input.

Thanks,
Ryan---

--
RyGuy


Douglas J. Steele said:
Did you actually change the name of the recordset? If so, you need to change
it in the line that fails as well.

If you didn't change the name of the recordset, be aware that there's no
Edit method for ADO recordsets.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


ryguy7272 said:
Hummm, never had to do that before, but ok. Now I have these lines in the
module:

Dim myADORecordset As ADODB.Recordset
Dim myDAORecordset As DAO.Recordset


Now, when I run the code I get an error that reads:
'Compile Error: Method or data error not found'

Code fails on this line:
RS.Edit

Foiled for now; hopefully not for long.

Any other suggestions?


Thanks,
Ryan---

--
RyGuy


Henk said:
Check this out:
http://www.thepoorhouse.org.uk/run_time_error_13_and_recordsets_in_access
regards
Henk

ryguy7272 skrev:

I followed the example at this site:
http://support.microsoft.com/kb/318881

It didn't work after I modified the code slightly; trying to use this
code:

Sub SendMessages(Optional AttachmentPath)
Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMailingList")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![EmailAddress]

With objOutlookMsg
' Add the To recipients to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo

' Add the Cc recipients to the e-mail message.
If (IsNull(Forms!frmMail!CCAddress)) Then
Else
Set objOutlookRecip =
.Recipients.Add(Forms!frmMail!CCAddress)
objOutlookRecip.Type = olCC
End If

' Set the Subject, the Body, and the Importance of the e-mail
message.
.Subject = Forms!frmMail!Subject
.Body = Forms!frmMail!MainText
.Importance = olImportanceHigh 'High importance

'Add the attachment to the e-mail message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If

' Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

Private Sub SendMessg_Click()
Call SendMessages
End Sub

I get this message:
"Run Time Error '13'"
Type Mismatch

Error occurs on this line:
Set MyRS = MyDB.OpenRecordset("tblMailingList")


References are set to
Outlook 10.0 Object Library
Microsoft DAO 3.6 Object Library

I have three text files which I wanted to use as attachments. All are
located in this directory:
C:\Documents and Settings\ThinkPad\Desktop\Text Files

I am just trying to find an easy way to send out a few dozen emails,
based
on email addresses saved in a table, which I use for work. One thing
that I
am doing different is that I am trying to run the code from a Command
Button;
no big deal right. I don't want to go into the DeBug Window and type
any
commands. Should be pretty straightforward; just not seeing the answer
here.. Any help would be greatly appreciated!!

Below, I found a great example of sending multiple emails:
http://www.datastrat.com/DataStrat2.html
'EmailSenate2K'
This is similar to what I want to do, but it uses a ListBox to display
all
(names) recipients of the emails. This is pretty clever; I am just
trying to
figure out a way to send multiple emails to people in my office, once
per
week, to notify them of updates.

Regards,
Ryan---
Click to expand...
Click to expand...
Click to expand...
 
How about posting what the code currently looks like?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


ryguy7272 said:
Thanks for the tip Doug. Now, It fails on this line:
Set MyRS = MyDB.OpenRecordset("tblMailingList")

The Table is named as 'tblMailingList'. I don't think that is thge
problem,
but when I place the mouse over the error-line, I see the balloon message
and
this...'MyRS = Nothing'. Very bizarre! How could it be nothing? I am
not
an Access expert by any means, but this seems pretty
straightforward...just
can't see the problem here...

I'd welcome any more input.

Thanks,
Ryan---

--
RyGuy


Douglas J. Steele said:
Did you actually change the name of the recordset? If so, you need to
change
it in the line that fails as well.

If you didn't change the name of the recordset, be aware that there's no
Edit method for ADO recordsets.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


ryguy7272 said:
Hummm, never had to do that before, but ok. Now I have these lines in
the
module:

Dim myADORecordset As ADODB.Recordset
Dim myDAORecordset As DAO.Recordset


Now, when I run the code I get an error that reads:
'Compile Error: Method or data error not found'

Code fails on this line:
RS.Edit

Foiled for now; hopefully not for long.

Any other suggestions?


Thanks,
Ryan---

--
RyGuy


:

Check this out:
http://www.thepoorhouse.org.uk/run_time_error_13_and_recordsets_in_access
regards
Henk

ryguy7272 skrev:

I followed the example at this site:
http://support.microsoft.com/kb/318881

It didn't work after I modified the code slightly; trying to use
this
code:

Sub SendMessages(Optional AttachmentPath)
Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMailingList")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![EmailAddress]

With objOutlookMsg
' Add the To recipients to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo

' Add the Cc recipients to the e-mail message.
If (IsNull(Forms!frmMail!CCAddress)) Then
Else
Set objOutlookRecip =
.Recipients.Add(Forms!frmMail!CCAddress)
objOutlookRecip.Type = olCC
End If

' Set the Subject, the Body, and the Importance of the
e-mail
message.
.Subject = Forms!frmMail!Subject
.Body = Forms!frmMail!MainText
.Importance = olImportanceHigh 'High importance

'Add the attachment to the e-mail message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If

' Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

Private Sub SendMessg_Click()
Call SendMessages
End Sub

I get this message:
"Run Time Error '13'"
Type Mismatch

Error occurs on this line:
Set MyRS = MyDB.OpenRecordset("tblMailingList")


References are set to
Outlook 10.0 Object Library
Microsoft DAO 3.6 Object Library

I have three text files which I wanted to use as attachments. All
are
located in this directory:
C:\Documents and Settings\ThinkPad\Desktop\Text Files

I am just trying to find an easy way to send out a few dozen emails,
based
on email addresses saved in a table, which I use for work. One
thing
that I
am doing different is that I am trying to run the code from a
Command
Button;
no big deal right. I don't want to go into the DeBug Window and
type
any
commands. Should be pretty straightforward; just not seeing the
answer
here.. Any help would be greatly appreciated!!

Below, I found a great example of sending multiple emails:
http://www.datastrat.com/DataStrat2.html
'EmailSenate2K'
This is similar to what I want to do, but it uses a ListBox to
display
all
(names) recipients of the emails. This is pretty clever; I am just
trying to
figure out a way to send multiple emails to people in my office,
once
per
week, to notify them of updates.

Regards,
Ryan---
Click to expand...
Click to expand...
Click to expand...
 
Here is my code...

Form_frmMail:
Private Sub SendMessg_Click()
Call SendMessages
End Sub

The Form has:
First TextBox:
Name = EmailAddress
ControlSource = EmailAddress

Second TextBox:
Name = CCAddress
Control Source = ‘…nothing here

Third TextBox:
Name = Subject
Control Source = ‘…nothing here

Forth TextBox:
Name = MainText
Control Source = ‘…nothing here

CommandButton:
Name = SendMssg
Caption = Send


Module1:
Option Compare Database
Option Explicit

Sub SendMessages(Optional AttachmentPath)

'Dim myADORecordset As ADODB.Recordset
Dim myDAORecordset As DAO.Recordset

Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMailingList")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![EmailAddress]

With objOutlookMsg
' Add the To recipients to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo

' Add the Cc recipients to the e-mail message.
If (IsNull(Forms!frmMail!CCAddress)) Then
Else
Set objOutlookRecip = .Recipients.Add(Forms!frmMail!CCAddress)
objOutlookRecip.Type = olCC
End If

' Set the Subject, the Body, and the Importance of the e-mail message.
.Subject = Forms!frmMail!Subject
.Body = Forms!frmMail!MainText
.Importance = olImportanceHigh 'High importance

'Add the attachment to the e-mail message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If

' Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

I have references to:
Visual Basic for Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.5 Library
Microsoft Outlook 11.0 Object Library
Microsoft DAO 3.6 Object Library
....got rid of the reference to ADO

I believe that is everything.
Thanks Doug!! If I can find the solution I will post back.

--
RyGuy


Douglas J. Steele said:
How about posting what the code currently looks like?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


ryguy7272 said:
Thanks for the tip Doug. Now, It fails on this line:
Set MyRS = MyDB.OpenRecordset("tblMailingList")

The Table is named as 'tblMailingList'. I don't think that is thge
problem,
but when I place the mouse over the error-line, I see the balloon message
and
this...'MyRS = Nothing'. Very bizarre! How could it be nothing? I am
not
an Access expert by any means, but this seems pretty
straightforward...just
can't see the problem here...

I'd welcome any more input.

Thanks,
Ryan---

--
RyGuy


Douglas J. Steele said:
Did you actually change the name of the recordset? If so, you need to
change
it in the line that fails as well.

If you didn't change the name of the recordset, be aware that there's no
Edit method for ADO recordsets.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


Hummm, never had to do that before, but ok. Now I have these lines in
the
module:

Dim myADORecordset As ADODB.Recordset
Dim myDAORecordset As DAO.Recordset


Now, when I run the code I get an error that reads:
'Compile Error: Method or data error not found'

Code fails on this line:
RS.Edit

Foiled for now; hopefully not for long.

Any other suggestions?


Thanks,
Ryan---

--
RyGuy


:

Check this out:
http://www.thepoorhouse.org.uk/run_time_error_13_and_recordsets_in_access
regards
Henk

ryguy7272 skrev:

I followed the example at this site:
http://support.microsoft.com/kb/318881

It didn't work after I modified the code slightly; trying to use
this
code:

Sub SendMessages(Optional AttachmentPath)
Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMailingList")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![EmailAddress]

With objOutlookMsg
' Add the To recipients to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo

' Add the Cc recipients to the e-mail message.
If (IsNull(Forms!frmMail!CCAddress)) Then
Else
Set objOutlookRecip =
.Recipients.Add(Forms!frmMail!CCAddress)
objOutlookRecip.Type = olCC
End If

' Set the Subject, the Body, and the Importance of the
e-mail
message.
.Subject = Forms!frmMail!Subject
.Body = Forms!frmMail!MainText
.Importance = olImportanceHigh 'High importance

'Add the attachment to the e-mail message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If

' Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

Private Sub SendMessg_Click()
Call SendMessages
End Sub

I get this message:
"Run Time Error '13'"
Type Mismatch

Error occurs on this line:
Set MyRS = MyDB.OpenRecordset("tblMailingList")


References are set to
Outlook 10.0 Object Library
Microsoft DAO 3.6 Object Library

I have three text files which I wanted to use as attachments. All
are
located in this directory:
C:\Documents and Settings\ThinkPad\Desktop\Text Files

I am just trying to find an easy way to send out a few dozen emails,
based
on email addresses saved in a table, which I use for work. One
thing
that I
am doing different is that I am trying to run the code from a
Command
Button;
no big deal right. I don't want to go into the DeBug Window and
type
any
commands. Should be pretty straightforward; just not seeing the
answer
here.. Any help would be greatly appreciated!!

Below, I found a great example of sending multiple emails:
http://www.datastrat.com/DataStrat2.html
'EmailSenate2K'
This is similar to what I want to do, but it uses a ListBox to
display
all
(names) recipients of the emails. This is pretty clever; I am just
trying to
figure out a way to send multiple emails to people in my office,
once
per
week, to notify them of updates.

Regards,
Ryan---
Click to expand...
Click to expand...
Click to expand...
 
You may have declared myDAORecordset as a DAO.Recordset, but you're not
using it!

Sub SendMessages(Optional AttachmentPath)

Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMailingList")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![EmailAddress]

With objOutlookMsg
' Add the To recipients to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo

' Add the Cc recipients to the e-mail message.
If (IsNull(Forms!frmMail!CCAddress)) Then
Else
Set objOutlookRecip = .Recipients.Add(Forms!frmMail!CCAddress)
objOutlookRecip.Type = olCC
End If

' Set the Subject, the Body, and the Importance of the e-mail
message.
.Subject = Forms!frmMail!Subject
.Body = Forms!frmMail!MainText
.Importance = olImportanceHigh 'High importance

'Add the attachment to the e-mail message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If

' Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


ryguy7272 said:
Here is my code...

Form_frmMail:
Private Sub SendMessg_Click()
Call SendMessages
End Sub

The Form has:
First TextBox:
Name = EmailAddress
ControlSource = EmailAddress

Second TextBox:
Name = CCAddress
Control Source = '.nothing here

Third TextBox:
Name = Subject
Control Source = '.nothing here

Forth TextBox:
Name = MainText
Control Source = '.nothing here

CommandButton:
Name = SendMssg
Caption = Send


Module1:
Option Compare Database
Option Explicit

Sub SendMessages(Optional AttachmentPath)

'Dim myADORecordset As ADODB.Recordset
Dim myDAORecordset As DAO.Recordset

Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMailingList")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![EmailAddress]

With objOutlookMsg
' Add the To recipients to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo

' Add the Cc recipients to the e-mail message.
If (IsNull(Forms!frmMail!CCAddress)) Then
Else
Set objOutlookRecip = .Recipients.Add(Forms!frmMail!CCAddress)
objOutlookRecip.Type = olCC
End If

' Set the Subject, the Body, and the Importance of the e-mail
message.
.Subject = Forms!frmMail!Subject
.Body = Forms!frmMail!MainText
.Importance = olImportanceHigh 'High importance

'Add the attachment to the e-mail message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If

' Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

I have references to:
Visual Basic for Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.5 Library
Microsoft Outlook 11.0 Object Library
Microsoft DAO 3.6 Object Library
...got rid of the reference to ADO

I believe that is everything.
Thanks Doug!! If I can find the solution I will post back.

--
RyGuy


Douglas J. Steele said:
How about posting what the code currently looks like?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


ryguy7272 said:
Thanks for the tip Doug. Now, It fails on this line:
Set MyRS = MyDB.OpenRecordset("tblMailingList")

The Table is named as 'tblMailingList'. I don't think that is thge
problem,
but when I place the mouse over the error-line, I see the balloon
message
and
this...'MyRS = Nothing'. Very bizarre! How could it be nothing? I am
not
an Access expert by any means, but this seems pretty
straightforward...just
can't see the problem here...

I'd welcome any more input.

Thanks,
Ryan---

--
RyGuy


:

Did you actually change the name of the recordset? If so, you need to
change
it in the line that fails as well.

If you didn't change the name of the recordset, be aware that there's
no
Edit method for ADO recordsets.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


Hummm, never had to do that before, but ok. Now I have these lines
in
the
module:

Dim myADORecordset As ADODB.Recordset
Dim myDAORecordset As DAO.Recordset


Now, when I run the code I get an error that reads:
'Compile Error: Method or data error not found'

Code fails on this line:
RS.Edit

Foiled for now; hopefully not for long.

Any other suggestions?


Thanks,
Ryan---

--
RyGuy


:

Check this out:
http://www.thepoorhouse.org.uk/run_time_error_13_and_recordsets_in_access
regards
Henk

ryguy7272 skrev:

I followed the example at this site:
http://support.microsoft.com/kb/318881

It didn't work after I modified the code slightly; trying to use
this
code:

Sub SendMessages(Optional AttachmentPath)
Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMailingList")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![EmailAddress]

With objOutlookMsg
' Add the To recipients to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo

' Add the Cc recipients to the e-mail message.
If (IsNull(Forms!frmMail!CCAddress)) Then
Else
Set objOutlookRecip =
.Recipients.Add(Forms!frmMail!CCAddress)
objOutlookRecip.Type = olCC
End If

' Set the Subject, the Body, and the Importance of the
e-mail
message.
.Subject = Forms!frmMail!Subject
.Body = Forms!frmMail!MainText
.Importance = olImportanceHigh 'High importance

'Add the attachment to the e-mail message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach =
.Attachments.Add(AttachmentPath)
End If

' Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

Private Sub SendMessg_Click()
Call SendMessages
End Sub

I get this message:
"Run Time Error '13'"
Type Mismatch

Error occurs on this line:
Set MyRS = MyDB.OpenRecordset("tblMailingList")


References are set to
Outlook 10.0 Object Library
Microsoft DAO 3.6 Object Library

I have three text files which I wanted to use as attachments.
All
are
located in this directory:
C:\Documents and Settings\ThinkPad\Desktop\Text Files

I am just trying to find an easy way to send out a few dozen
emails,
based
on email addresses saved in a table, which I use for work. One
thing
that I
am doing different is that I am trying to run the code from a
Command
Button;
no big deal right. I don't want to go into the DeBug Window and
type
any
commands. Should be pretty straightforward; just not seeing the
answer
here.. Any help would be greatly appreciated!!

Below, I found a great example of sending multiple emails:
http://www.datastrat.com/DataStrat2.html
'EmailSenate2K'
This is similar to what I want to do, but it uses a ListBox to
display
all
(names) recipients of the emails. This is pretty clever; I am
just
trying to
figure out a way to send multiple emails to people in my office,
once
per
week, to notify them of updates.

Regards,
Ryan---
Click to expand...
Click to expand...
Click to expand...
 
Thanks Doug! That was exactly it! Please respond to one more question, if
it is not too much trouble. How do I attach several files, all in the same
folder, ot these emails that I am sending out? I tried to add another
TextBox to my frmMail. I named it 'AttachmentPath' and mapped it to a file
that I have on my Desktop. When the macro fires, the email goes, but no
attachment is sent.

I would suspect that this line of code controls the attachments:
Set objOutlookAttach = .Attachments.Add(AttachmentPath)

That's why I named the TextBox 'AttachmentPath'.

Anyway, that wouldn't even send one file, and I wanted to be able to send
multiple files. I tried code like the following:
'Add the attachment to the e-mail message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)

strFolder = "C:\Documents and Settings\ThinkPad\Desktop\Text Files\"
strFile = Dir$(strFolder & "*.txt")
Do While Len(strFile) > 0

strFile = Dir$()
Loop

End If
'etc. etc. etc.

That didn't work either!! Ideally, I'd like to enter the path in my new
TextBox, named 'AttachmentPath'. Do I have to do something with the
ControlSource? I am trying to make this as easy as possible to use.
Eventually, others will probably use this DB, and I wouldn't expect them to
go into the VB Editor and modify code.

I am sure it has something to do with the Dir$() function, because I've used
this before (importing several excel files into a Tables; even worked with
multiple tables). I'd be most appreciative if you could answer this last
question Doug. Everything else works great!!!

Thanks for all the effort,
Ryan---

--
RyGuy


Douglas J. Steele said:
You may have declared myDAORecordset as a DAO.Recordset, but you're not
using it!

Sub SendMessages(Optional AttachmentPath)

Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMailingList")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![EmailAddress]

With objOutlookMsg
' Add the To recipients to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo

' Add the Cc recipients to the e-mail message.
If (IsNull(Forms!frmMail!CCAddress)) Then
Else
Set objOutlookRecip = .Recipients.Add(Forms!frmMail!CCAddress)
objOutlookRecip.Type = olCC
End If

' Set the Subject, the Body, and the Importance of the e-mail
message.
.Subject = Forms!frmMail!Subject
.Body = Forms!frmMail!MainText
.Importance = olImportanceHigh 'High importance

'Add the attachment to the e-mail message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If

' Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


ryguy7272 said:
Here is my code...

Form_frmMail:
Private Sub SendMessg_Click()
Call SendMessages
End Sub

The Form has:
First TextBox:
Name = EmailAddress
ControlSource = EmailAddress

Second TextBox:
Name = CCAddress
Control Source = '.nothing here

Third TextBox:
Name = Subject
Control Source = '.nothing here

Forth TextBox:
Name = MainText
Control Source = '.nothing here

CommandButton:
Name = SendMssg
Caption = Send


Module1:
Option Compare Database
Option Explicit

Sub SendMessages(Optional AttachmentPath)

'Dim myADORecordset As ADODB.Recordset
Dim myDAORecordset As DAO.Recordset

Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMailingList")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![EmailAddress]

With objOutlookMsg
' Add the To recipients to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo

' Add the Cc recipients to the e-mail message.
If (IsNull(Forms!frmMail!CCAddress)) Then
Else
Set objOutlookRecip = .Recipients.Add(Forms!frmMail!CCAddress)
objOutlookRecip.Type = olCC
End If

' Set the Subject, the Body, and the Importance of the e-mail
message.
.Subject = Forms!frmMail!Subject
.Body = Forms!frmMail!MainText
.Importance = olImportanceHigh 'High importance

'Add the attachment to the e-mail message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If

' Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

I have references to:
Visual Basic for Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.5 Library
Microsoft Outlook 11.0 Object Library
Microsoft DAO 3.6 Object Library
...got rid of the reference to ADO

I believe that is everything.
Thanks Doug!! If I can find the solution I will post back.

--
RyGuy


Douglas J. Steele said:
How about posting what the code currently looks like?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


Thanks for the tip Doug. Now, It fails on this line:
Set MyRS = MyDB.OpenRecordset("tblMailingList")

The Table is named as 'tblMailingList'. I don't think that is thge
problem,
but when I place the mouse over the error-line, I see the balloon
message
and
this...'MyRS = Nothing'. Very bizarre! How could it be nothing? I am
not
an Access expert by any means, but this seems pretty
straightforward...just
can't see the problem here...

I'd welcome any more input.

Thanks,
Ryan---

--
RyGuy


:

Did you actually change the name of the recordset? If so, you need to
change
it in the line that fails as well.

If you didn't change the name of the recordset, be aware that there's
no
Edit method for ADO recordsets.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


Hummm, never had to do that before, but ok. Now I have these lines
in
the
module:

Dim myADORecordset As ADODB.Recordset
Dim myDAORecordset As DAO.Recordset


Now, when I run the code I get an error that reads:
'Compile Error: Method or data error not found'

Code fails on this line:
RS.Edit

Foiled for now; hopefully not for long.

Any other suggestions?


Thanks,
Ryan---

--
RyGuy


:

Check this out:
http://www.thepoorhouse.org.uk/run_time_error_13_and_recordsets_in_access
regards
Henk

ryguy7272 skrev:

I followed the example at this site:
http://support.microsoft.com/kb/318881

It didn't work after I modified the code slightly; trying to use
this
code:

Sub SendMessages(Optional AttachmentPath)
Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMailingList")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
Click to expand...
Click to expand...
Click to expand...
 
You should use the same Forms!NameOfForm!AttachmentPath syntax that you're
using to get the addresses.

Another issue is your use of the IsMissing function. The IsMissing function
is intended to detect whether or not optional Variant arguments have been
provided in calling a procedure. I believe you need the IsNull function.

In your second attempt, you're getting the file names into strFile, but
you're not doing anything with strFile. (As well, recognize that strFile
only contains the file path: you'll need strFolder & strFile in order to
address the specific file)

There might be something relevant in the Access EMail FAQ that Tony Toews
has at http://www.granite.ab.ca/access/email.htm

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


ryguy7272 said:
Thanks Doug! That was exactly it! Please respond to one more question,
if
it is not too much trouble. How do I attach several files, all in the
same
folder, ot these emails that I am sending out? I tried to add another
TextBox to my frmMail. I named it 'AttachmentPath' and mapped it to a
file
that I have on my Desktop. When the macro fires, the email goes, but no
attachment is sent.

I would suspect that this line of code controls the attachments:
Set objOutlookAttach = .Attachments.Add(AttachmentPath)

That's why I named the TextBox 'AttachmentPath'.

Anyway, that wouldn't even send one file, and I wanted to be able to send
multiple files. I tried code like the following:
'Add the attachment to the e-mail message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)

strFolder = "C:\Documents and Settings\ThinkPad\Desktop\Text Files\"
strFile = Dir$(strFolder & "*.txt")
Do While Len(strFile) > 0

strFile = Dir$()
Loop

End If
'etc. etc. etc.

That didn't work either!! Ideally, I'd like to enter the path in my new
TextBox, named 'AttachmentPath'. Do I have to do something with the
ControlSource? I am trying to make this as easy as possible to use.
Eventually, others will probably use this DB, and I wouldn't expect them
to
go into the VB Editor and modify code.

I am sure it has something to do with the Dir$() function, because I've
used
this before (importing several excel files into a Tables; even worked with
multiple tables). I'd be most appreciative if you could answer this last
question Doug. Everything else works great!!!

Thanks for all the effort,
Ryan---

--
RyGuy


Douglas J. Steele said:
You may have declared myDAORecordset as a DAO.Recordset, but you're not
using it!

Sub SendMessages(Optional AttachmentPath)

Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMailingList")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![EmailAddress]

With objOutlookMsg
' Add the To recipients to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo

' Add the Cc recipients to the e-mail message.
If (IsNull(Forms!frmMail!CCAddress)) Then
Else
Set objOutlookRecip = .Recipients.Add(Forms!frmMail!CCAddress)
objOutlookRecip.Type = olCC
End If

' Set the Subject, the Body, and the Importance of the e-mail
message.
.Subject = Forms!frmMail!Subject
.Body = Forms!frmMail!MainText
.Importance = olImportanceHigh 'High importance

'Add the attachment to the e-mail message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If

' Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


ryguy7272 said:
Here is my code...

Form_frmMail:
Private Sub SendMessg_Click()
Call SendMessages
End Sub

The Form has:
First TextBox:
Name = EmailAddress
ControlSource = EmailAddress

Second TextBox:
Name = CCAddress
Control Source = '.nothing here

Third TextBox:
Name = Subject
Control Source = '.nothing here

Forth TextBox:
Name = MainText
Control Source = '.nothing here

CommandButton:
Name = SendMssg
Caption = Send


Module1:
Option Compare Database
Option Explicit

Sub SendMessages(Optional AttachmentPath)

'Dim myADORecordset As ADODB.Recordset
Dim myDAORecordset As DAO.Recordset

Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMailingList")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![EmailAddress]

With objOutlookMsg
' Add the To recipients to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo

' Add the Cc recipients to the e-mail message.
If (IsNull(Forms!frmMail!CCAddress)) Then
Else
Set objOutlookRecip =
.Recipients.Add(Forms!frmMail!CCAddress)
objOutlookRecip.Type = olCC
End If

' Set the Subject, the Body, and the Importance of the e-mail
message.
.Subject = Forms!frmMail!Subject
.Body = Forms!frmMail!MainText
.Importance = olImportanceHigh 'High importance

'Add the attachment to the e-mail message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If

' Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

I have references to:
Visual Basic for Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.5 Library
Microsoft Outlook 11.0 Object Library
Microsoft DAO 3.6 Object Library
...got rid of the reference to ADO

I believe that is everything.
Thanks Doug!! If I can find the solution I will post back.

--
RyGuy


:

How about posting what the code currently looks like?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


Thanks for the tip Doug. Now, It fails on this line:
Set MyRS = MyDB.OpenRecordset("tblMailingList")

The Table is named as 'tblMailingList'. I don't think that is thge
problem,
but when I place the mouse over the error-line, I see the balloon
message
and
this...'MyRS = Nothing'. Very bizarre! How could it be nothing? I
am
not
an Access expert by any means, but this seems pretty
straightforward...just
can't see the problem here...

I'd welcome any more input.

Thanks,
Ryan---

--
RyGuy


:

Did you actually change the name of the recordset? If so, you need
to
change
it in the line that fails as well.

If you didn't change the name of the recordset, be aware that
there's
no
Edit method for ADO recordsets.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


Hummm, never had to do that before, but ok. Now I have these
lines
in
the
module:

Dim myADORecordset As ADODB.Recordset
Dim myDAORecordset As DAO.Recordset


Now, when I run the code I get an error that reads:
'Compile Error: Method or data error not found'

Code fails on this line:
RS.Edit

Foiled for now; hopefully not for long.

Any other suggestions?


Thanks,
Ryan---

--
RyGuy


:

Check this out:
http://www.thepoorhouse.org.uk/run_time_error_13_and_recordsets_in_access
regards
Henk

ryguy7272 skrev:

I followed the example at this site:
http://support.microsoft.com/kb/318881

It didn't work after I modified the code slightly; trying to
use
this
code:

Sub SendMessages(Optional AttachmentPath)
Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMailingList")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
Click to expand...
Click to expand...
Click to expand...
 
Thanks for the link!! Thanks, also, for everything!! I've got to learn much
more about Access. Now is as good a time as any, to get started. I guess
I'll begin with those attachments...try to figure it out on my own...

Thanks again!!
Ryan---

--
RyGuy


Douglas J. Steele said:
You should use the same Forms!NameOfForm!AttachmentPath syntax that you're
using to get the addresses.

Another issue is your use of the IsMissing function. The IsMissing function
is intended to detect whether or not optional Variant arguments have been
provided in calling a procedure. I believe you need the IsNull function.

In your second attempt, you're getting the file names into strFile, but
you're not doing anything with strFile. (As well, recognize that strFile
only contains the file path: you'll need strFolder & strFile in order to
address the specific file)

There might be something relevant in the Access EMail FAQ that Tony Toews
has at http://www.granite.ab.ca/access/email.htm

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


ryguy7272 said:
Thanks Doug! That was exactly it! Please respond to one more question,
if
it is not too much trouble. How do I attach several files, all in the
same
folder, ot these emails that I am sending out? I tried to add another
TextBox to my frmMail. I named it 'AttachmentPath' and mapped it to a
file
that I have on my Desktop. When the macro fires, the email goes, but no
attachment is sent.

I would suspect that this line of code controls the attachments:
Set objOutlookAttach = .Attachments.Add(AttachmentPath)

That's why I named the TextBox 'AttachmentPath'.

Anyway, that wouldn't even send one file, and I wanted to be able to send
multiple files. I tried code like the following:
'Add the attachment to the e-mail message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)

strFolder = "C:\Documents and Settings\ThinkPad\Desktop\Text Files\"
strFile = Dir$(strFolder & "*.txt")
Do While Len(strFile) > 0

strFile = Dir$()
Loop

End If
'etc. etc. etc.

That didn't work either!! Ideally, I'd like to enter the path in my new
TextBox, named 'AttachmentPath'. Do I have to do something with the
ControlSource? I am trying to make this as easy as possible to use.
Eventually, others will probably use this DB, and I wouldn't expect them
to
go into the VB Editor and modify code.

I am sure it has something to do with the Dir$() function, because I've
used
this before (importing several excel files into a Tables; even worked with
multiple tables). I'd be most appreciative if you could answer this last
question Doug. Everything else works great!!!

Thanks for all the effort,
Ryan---

--
RyGuy


Douglas J. Steele said:
You may have declared myDAORecordset as a DAO.Recordset, but you're not
using it!

Sub SendMessages(Optional AttachmentPath)

Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMailingList")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![EmailAddress]

With objOutlookMsg
' Add the To recipients to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo

' Add the Cc recipients to the e-mail message.
If (IsNull(Forms!frmMail!CCAddress)) Then
Else
Set objOutlookRecip = .Recipients.Add(Forms!frmMail!CCAddress)
objOutlookRecip.Type = olCC
End If

' Set the Subject, the Body, and the Importance of the e-mail
message.
.Subject = Forms!frmMail!Subject
.Body = Forms!frmMail!MainText
.Importance = olImportanceHigh 'High importance

'Add the attachment to the e-mail message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If

' Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


Here is my code...

Form_frmMail:
Private Sub SendMessg_Click()
Call SendMessages
End Sub

The Form has:
First TextBox:
Name = EmailAddress
ControlSource = EmailAddress

Second TextBox:
Name = CCAddress
Control Source = '.nothing here

Third TextBox:
Name = Subject
Control Source = '.nothing here

Forth TextBox:
Name = MainText
Control Source = '.nothing here

CommandButton:
Name = SendMssg
Caption = Send


Module1:
Option Compare Database
Option Explicit

Sub SendMessages(Optional AttachmentPath)

'Dim myADORecordset As ADODB.Recordset
Dim myDAORecordset As DAO.Recordset

Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMailingList")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![EmailAddress]

With objOutlookMsg
' Add the To recipients to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo

' Add the Cc recipients to the e-mail message.
If (IsNull(Forms!frmMail!CCAddress)) Then
Else
Set objOutlookRecip =
.Recipients.Add(Forms!frmMail!CCAddress)
objOutlookRecip.Type = olCC
End If
Click to expand...
' Set the Subject, the Body, and the Importance of the e-mail
message.
.Subject = Forms!frmMail!Subject
.Body = Forms!frmMail!MainText
.Importance = olImportanceHigh 'High importance

'Add the attachment to the e-mail message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If

' Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

I have references to:
Visual Basic for Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.5 Library
Microsoft Outlook 11.0 Object Library
Microsoft DAO 3.6 Object Library
...got rid of the reference to ADO

I believe that is everything.
Thanks Doug!! If I can find the solution I will post back.

--
RyGuy


:

How about posting what the code currently looks like?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


Thanks for the tip Doug. Now, It fails on this line:
Set MyRS = MyDB.OpenRecordset("tblMailingList")

The Table is named as 'tblMailingList'. I don't think that is thge
problem,
but when I place the mouse over the error-line, I see the balloon
message
and
this...'MyRS = Nothing'. Very bizarre! How could it be nothing? I
am
not
an Access expert by any means, but this seems pretty
straightforward...just
can't see the problem here...

I'd welcome any more input.

Thanks,
Ryan---

--
RyGuy


"Douglas J. Steele" wrote:
Click to expand...
Click to expand...
Click to expand...
 
I got it!! Perhaps this will help someone else, someday.

Sub SendMessages(Optional AttachmentPath)

Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMailingList")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![EmailAddress]

With objOutlookMsg
' Add the To recipients to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo

' Add the Cc recipients to the e-mail message.
If (IsNull(Forms!frmMail!CCAddress)) Then
Else
Set objOutlookRecip = .Recipients.Add(Forms!frmMail!CCAddress)
objOutlookRecip.Type = olCC
End If

' Set the Subject, the Body, and the Importance of the e-mail message.
..Subject = Forms!frmMail!Subject
..Body = Forms!frmMail!MainText
..Importance = olImportanceHigh 'High importance


' Add attachments to the message.
With Application.FileSearch
.LookIn = Forms!frmMail!Att
.FileName = "*.*"
.Execute

For i = 1 To .FoundFiles.Count
objOutlookMsg.Attachments.Add .FoundFiles(i)
Next i
End With

'That replaces this...
'Add the attachment to the e-mail message.
'If Not IsMissing(AttachmentPath) Then
'Set objOutlookAttach = .Attachments.Add(AttachmentPath)
'End If


' Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
..Send
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

Finally, check this out:
http://www.snapfiles.com/get/clickyes.html
This small, free, utility is VERY useful if you are planning to send
multiple emails to multiple recipients!!

Thanks for the push in the right direction Doug!!


Regards,
Ryan---

--
RyGuy


ryguy7272 said:
Thanks for the link!! Thanks, also, for everything!! I've got to learn much
more about Access. Now is as good a time as any, to get started. I guess
I'll begin with those attachments...try to figure it out on my own...

Thanks again!!
Ryan---

--
RyGuy


Douglas J. Steele said:
You should use the same Forms!NameOfForm!AttachmentPath syntax that you're
using to get the addresses.

Another issue is your use of the IsMissing function. The IsMissing function
is intended to detect whether or not optional Variant arguments have been
provided in calling a procedure. I believe you need the IsNull function.

In your second attempt, you're getting the file names into strFile, but
you're not doing anything with strFile. (As well, recognize that strFile
only contains the file path: you'll need strFolder & strFile in order to
address the specific file)

There might be something relevant in the Access EMail FAQ that Tony Toews
has at http://www.granite.ab.ca/access/email.htm

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


ryguy7272 said:
Thanks Doug! That was exactly it! Please respond to one more question,
if
it is not too much trouble. How do I attach several files, all in the
same
folder, ot these emails that I am sending out? I tried to add another
TextBox to my frmMail. I named it 'AttachmentPath' and mapped it to a
file
that I have on my Desktop. When the macro fires, the email goes, but no
attachment is sent.

I would suspect that this line of code controls the attachments:
Set objOutlookAttach = .Attachments.Add(AttachmentPath)

That's why I named the TextBox 'AttachmentPath'.

Anyway, that wouldn't even send one file, and I wanted to be able to send
multiple files. I tried code like the following:
'Add the attachment to the e-mail message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)

strFolder = "C:\Documents and Settings\ThinkPad\Desktop\Text Files\"
strFile = Dir$(strFolder & "*.txt")
Do While Len(strFile) > 0

strFile = Dir$()
Loop

End If
'etc. etc. etc.

That didn't work either!! Ideally, I'd like to enter the path in my new
TextBox, named 'AttachmentPath'. Do I have to do something with the
ControlSource? I am trying to make this as easy as possible to use.
Eventually, others will probably use this DB, and I wouldn't expect them
to
go into the VB Editor and modify code.

I am sure it has something to do with the Dir$() function, because I've
used
this before (importing several excel files into a Tables; even worked with
multiple tables). I'd be most appreciative if you could answer this last
question Doug. Everything else works great!!!

Thanks for all the effort,
Ryan---

--
RyGuy


:

You may have declared myDAORecordset as a DAO.Recordset, but you're not
using it!

Sub SendMessages(Optional AttachmentPath)

Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMailingList")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![EmailAddress]

With objOutlookMsg
' Add the To recipients to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo

' Add the Cc recipients to the e-mail message.
If (IsNull(Forms!frmMail!CCAddress)) Then
Else
Set objOutlookRecip = .Recipients.Add(Forms!frmMail!CCAddress)
objOutlookRecip.Type = olCC
End If

' Set the Subject, the Body, and the Importance of the e-mail
message.
.Subject = Forms!frmMail!Subject
.Body = Forms!frmMail!MainText
.Importance = olImportanceHigh 'High importance

'Add the attachment to the e-mail message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If

' Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


Here is my code...

Form_frmMail:
Private Sub SendMessg_Click()
Call SendMessages
End Sub

The Form has:
First TextBox:
Name = EmailAddress
ControlSource = EmailAddress

Second TextBox:
Name = CCAddress
Control Source = '.nothing here

Third TextBox:
Name = Subject
Control Source = '.nothing here

Forth TextBox:
Name = MainText
Control Source = '.nothing here

CommandButton:
Name = SendMssg
Caption = Send


Module1:
Option Compare Database
Option Explicit

Sub SendMessages(Optional AttachmentPath)

'Dim myADORecordset As ADODB.Recordset
Dim myDAORecordset As DAO.Recordset

Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMailingList")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![EmailAddress]

With objOutlookMsg
' Add the To recipients to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo

' Add the Cc recipients to the e-mail message.
If (IsNull(Forms!frmMail!CCAddress)) Then
Else
Set objOutlookRecip =
.Recipients.Add(Forms!frmMail!CCAddress)
objOutlookRecip.Type = olCC
End If


' Set the Subject, the Body, and the Importance of the e-mail
message.
.Subject = Forms!frmMail!Subject
.Body = Forms!frmMail!MainText
.Importance = olImportanceHigh 'High importance

'Add the attachment to the e-mail message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If

' Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

I have references to:
Visual Basic for Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.5 Library
Microsoft Outlook 11.0 Object Library
Microsoft DAO 3.6 Object Library
...got rid of the reference to ADO

I believe that is everything.
Thanks Doug!! If I can find the solution I will post back.

--
RyGuy


:

How about posting what the code currently looks like?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


Thanks for the tip Doug. Now, It fails on this line:
Set MyRS = MyDB.OpenRecordset("tblMailingList")

The Table is named as 'tblMailingList'. I don't think that is thge
problem,
but when I place the mouse over the error-line, I see the balloon
message
and
this...'MyRS = Nothing'. Very bizarre! How could it be nothing? I
am
not
an Access expert by any means, but this seems pretty
Click to expand...
Click to expand...
Click to expand...
 
Ry,

I am in the process of building this database, but alas, i am so used to doing all of the "plug and play" stuff, I am lacking the VBA knowledge that I need to build what you did before. I am soldier in the Army, recovering from my wounds and I now have short ter memory loss.

My question is, concerning the code below, what exactly do I input and what fields do I apply this to?

Any help will be most helpful

Dave
I followed the example at this site:
http://support.microsoft.com/kb/318881

It didn't work after I modified the code slightly; trying to use this code:

Sub SendMessages(Optional AttachmentPath)
Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMailingList")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![EmailAddress]

With objOutlookMsg
' Add the To recipients to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo

' Add the Cc recipients to the e-mail message.
If (IsNull(Forms!frmMail!CCAddress)) Then
Else
Set objOutlookRecip = .Recipients.Add(Forms!frmMail!CCAddress)
objOutlookRecip.Type = olCC
End If

' Set the Subject, the Body, and the Importance of the e-mail message.
.Subject = Forms!frmMail!Subject
.Body = Forms!frmMail!MainText
.Importance = olImportanceHigh 'High importance

'Add the attachment to the e-mail message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If

' Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

Private Sub SendMessg_Click()
Call SendMessages
End Sub

I get this message:
"Run Time Error '13'"
Type Mismatch

Error occurs on this line:
Set MyRS = MyDB.OpenRecordset("tblMailingList")


References are set to
Outlook 10.0 Object Library
Microsoft DAO 3.6 Object Library

I have three text files which I wanted to use as attachments. All are
located in this directory:
C:\Documents and Settings\ThinkPad\Desktop\Text Files

I am just trying to find an easy way to send out a few dozen emails, based
on email addresses saved in a table, which I use for work. One thing that I
am doing different is that I am trying to run the code from a Command Button;
no big deal right. I don???t want to go into the DeBug Window and type any
commands. Should be pretty straightforward; just not seeing the answer
here???. Any help would be greatly appreciated!!

Below, I found a great example of sending multiple emails:
http://www.datastrat.com/DataStrat2.html
???EmailSenate2K???
This is similar to what I want to do, but it uses a ListBox to display all
(names) recipients of the emails. This is pretty clever; I am just trying to
figure out a way to send multiple emails to people in my office, once per
week, to notify them of updates.

Regards,
Ryan---

--
RyGuy
http://www.thepoorhouse.org.uk/run_time_error_13_and_recordsets_in_access
regards
Henk

ryguy7272 skrev:
Click to expand...
Form_frmMail:
Private Sub SendMessg_Click()
Call SendMessages
End Sub

The Form has:
First TextBox:
Name = EmailAddress
ControlSource = EmailAddress

Second TextBox:
Name = CCAddress
Control Source = ??????nothing here

Third TextBox:
Name = Subject
Control Source = ??????nothing here

Forth TextBox:
Name = MainText
Control Source = ??????nothing here

CommandButton:
Name = SendMssg
Caption = Send


Module1:
Option Compare Database
Option Explicit

Sub SendMessages(Optional AttachmentPath)

'Dim myADORecordset As ADODB.Recordset
Dim myDAORecordset As DAO.Recordset

Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMailingList")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![EmailAddress]

With objOutlookMsg
' Add the To recipients to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo

' Add the Cc recipients to the e-mail message.
If (IsNull(Forms!frmMail!CCAddress)) Then
Else
Set objOutlookRecip = .Recipients.Add(Forms!frmMail!CCAddress)
objOutlookRecip.Type = olCC
End If

' Set the Subject, the Body, and the Importance of the e-mail message.
.Subject = Forms!frmMail!Subject
.Body = Forms!frmMail!MainText
.Importance = olImportanceHigh 'High importance

'Add the attachment to the e-mail message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If

' Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

I have references to:
Visual Basic for Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.5 Library
Microsoft Outlook 11.0 Object Library
Microsoft DAO 3.6 Object Library
...got rid of the reference to ADO

I believe that is everything.
Thanks Doug!! If I can find the solution I will post back.

--
RyGuy


"Douglas J. Steele" wrote:
Click to expand...
Click to expand...
On Monday, December 01, 2008 8:15 PM Douglas J. Steele wrote:
You may have declared myDAORecordset as a DAO.Recordset, but you're not
using it!

Sub SendMessages(Optional AttachmentPath)

Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMailingList")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![EmailAddress]

With objOutlookMsg
' Add the To recipients to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo

' Add the Cc recipients to the e-mail message.
If (IsNull(Forms!frmMail!CCAddress)) Then
Else
Set objOutlookRecip = .Recipients.Add(Forms!frmMail!CCAddress)
objOutlookRecip.Type = olCC
End If

' Set the Subject, the Body, and the Importance of the e-mail
message.
.Subject = Forms!frmMail!Subject
.Body = Forms!frmMail!MainText
.Importance = olImportanceHigh 'High importance

'Add the attachment to the e-mail message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If

' Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


news:[email protected]...
Click to expand...
Click to expand...
On Tuesday, December 02, 2008 4:26 PM ryguy727 wrote:
I got it!! Perhaps this will help someone else, someday.

Sub SendMessages(Optional AttachmentPath)

Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMailingList")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![EmailAddress]

With objOutlookMsg
' Add the To recipients to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo

' Add the Cc recipients to the e-mail message.
If (IsNull(Forms!frmMail!CCAddress)) Then
Else
Set objOutlookRecip = .Recipients.Add(Forms!frmMail!CCAddress)
objOutlookRecip.Type = olCC
End If

' Set the Subject, the Body, and the Importance of the e-mail message.
.Subject = Forms!frmMail!Subject
.Body = Forms!frmMail!MainText
.Importance = olImportanceHigh 'High importance


' Add attachments to the message.
With Application.FileSearch
.LookIn = Forms!frmMail!Att
.FileName = "*.*"
.Execute

For i = 1 To .FoundFiles.Count
objOutlookMsg.Attachments.Add .FoundFiles(i)
Next i
End With

'That replaces this...
'Add the attachment to the e-mail message.
'If Not IsMissing(AttachmentPath) Then
'Set objOutlookAttach = .Attachments.Add(AttachmentPath)
'End If


' Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

Finally, check this out:
http://www.snapfiles.com/get/clickyes.html
This small, free, utility is VERY useful if you are planning to send
multiple emails to multiple recipients!!

Thanks for the push in the right direction Doug!!


Regards,
Ryan---

--
RyGuy


"ryguy7272" wrote:
Click to expand...
Click to expand...
Click to expand...
 
Back
Top