Send to email, one record, one email Code getting error

[kaykrazy]

mdmilner I used some code that you supplied on a previous post. I've modified it. I need loop through my recordset and send an email for each record in the database. I have 2 test records in my database. The first one sends no problem (although I get the message asking if I really want to email. I'd like to get rid of it)
When the code loops, it picks up the second record, but fails on:
olMail.To = strEmailAddress

Sub SetRecipients()
Dim olApp As Object, olMail As Object
'Dim rngeAddresses As Range, rngeCell As Range
Dim strRecipients As String
Dim strEmailAddress As String
Dim dbUserID As Double
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

Set dbs = CurrentDb()

strSQL = "SELECT * FROM tblContact"

Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(olMailitem)


Set rs = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

With rs
Do Until rs.EOF

strRecipients = .Fields(2).Value
strEmailAddress = .Fields(1).Value
dbUserID = .Fields(0).Value

'format e-mail
olMail.To = strEmailAddress
'olMail.Attachments.Add ReportName
olMail.Subject = Date & " Report"
olMail.Body = strRecipients & " Here is your UserID: " & strUserID
olMail.DeleteAfterSubmit = True
olMail.Send
.MoveNext
Loop
End With


End Sub


Thanks for any help you can provide

Kathy

 

[Norie]

You say it fails, but how?

Have you tried stepping through the code using F8 and checking that the variables are OK.

 

[kaykrazy]

Re: Send to email, one record, one email Code getting erro

Yes, I did step through my code. The error is


Run-time error '-1733033719 (98b4010q)':
Method 'To' of Object '_MailTo' failed

I thinks its something to do with resetting/closing out the mail.

The example I copied from looped through the record set to concatenate email addresses to send one email message.

I am trying to read thorough the recordset but send an email for each record in the table.

I'm hoping to get a solution because otherwise I'm going to have to maually send each person their id. There are about 500 people.

Kathy

 

[bat17]

I think that you need to move the"Set olApp = CreateObject("Outlook.Application")
" inside the loop.

Sub SetRecipients()
Dim olApp As Object, olMail As Object
'Dim rngeAddresses As Range, rngeCell As Range
Dim strRecipients As String
Dim strEmailAddress As String
Dim dbUserID As Double
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

Set dbs = CurrentDb()
strSQL = "SELECT * FROM tblContact"
Set rs = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
Set olApp = CreateObject("Outlook.Application")


With rs
   Do Until rs.EOF
      Set olMail = olApp.CreateItem(olMailitem)
      strRecipients = .Fields(2).Value
      strEmailAddress = .Fields(1).Value
      dbUserID = .Fields(0).Value
      
      'format e-mail
      olMail.To = strEmailAddress
      'olMail.Attachments.Add ReportName
      olMail.Subject = DATE & " Report"
      olMail.Body = strRecipients & " Here is your UserID: " & dbUserID
      olMail.DeleteAfterSubmit = True
      olMail.Send
      .MoveNext
   Loop
End With

Set olApp = Nothing
Set rs = Nothing
Set dbs = Nothing
End Sub

Also I notice that you have "Dim dbUserID As Double" but used "olMail.Body = strRecipients & " Here is your UserID: " & strUserID " in your code.

If you add "Option Explicit" (No quotes!) to the top of your code modules it will check that you have declared all of your variables properly.

HTH

Peter

Edited to correct code

 

[kaykrazy]

Re: Send to email, one record, one email Code getting erro

Thank you very much. That works.
Is there anyway to turn off the security message for each of the emails sent without using the redemption program? What about sendkey? This will probably be used only 2 or 3 times til everyone receives their id.


Thanks again. I love this board.
Kathy

 

[mdmilner]

Re: Send to email, one record, one email Code getting erro

Posted a reply but it seems Bat17's suggestion did work.
The original code was somebody elses here:
http://www.mrexcel.com/board2/viewtopic.php?t=87487&highlight=email

Using the above, I get the message every time - it's a built in method requiring human intervention to help slow down the trojan-email-virus thing that's been going around these last few years.

But, what I have noticed is that if you use the DoCmd.SendObject method it doesn't trigger the warning. Different method - not as flexible, has some limitations but works pretty well for me.

Mike

 

[kaykrazy]

Re: Send to email, one record, one email Code getting erro

thanks mdmilner. Yes that's the example I used to craft my code. Their problem was different though -- they were asking to not have the emails fill up their sent files. I'm not so worried about.


I will try the docmd.sendObject. do I use it in place of the olMail.send?

 

[mdmilner]

Re: Send to email, one record, one email Code getting erro

No, it's completely different.
Jump into the Access help and you'll see it.
Here's a sample line from one of my jobs:

 DoCmd.SendObject acSendNoObject, "", "acFormatRTF", strAddr, _
              strCAddr, "", strSubject, strGreet & strBody, True, ""

This just sends a RTF file and needs things like the TO/CC/Subject/Body information. The above are just the variable names I chose to use.

This will add items to your sentmail folder if that's set by default (it's an option within Outlook). Why not just let it fill up and delete them?

You know the MS Office trick where you left click the first item -- scroll down to the last item, hold the shift-key and then left-click the last item...right? That should select every item in your sent mail folder between the two clicks. A single tap of the delete button (keyboard button) removes them all.

Mike

 

[kaykrazy]

Re: Send to email, one record, one email Code getting erro

mdmilner, I took your suggestion and tried the docmd.sendobject.

It comes up with a box asking me for a profile (outlook is the default) I don't have a clue what to do with this. I hit OK and then the email message displayed (apparently not sent) and then for the second one I got the same msg box askingfor profile.

Obviously I'm missing something