Get email addresses from table

[pld60]

RogerH,

I think we are very very close. When I run this I get a Run-time error 3021 No Current Record, and it shows the error at

strEmailAdds = strEmailAdds & "; " & objMyTable.email

When I hover over strEmailAdds it shows all of the email addresses from my field with ; between them.

What is wrong?

 

[Gasman]

EDIT: you have to do movenext BEFORE adding the 2nd address, or it puts the 1st adds twice

PMFJI,

Could you not code it as

objMyTable.MoveFirst

Do While Not objMyTable.EOF
   strEmailAdds =strEmailAdds & objMyTable.email &";"  
   objMyTable.MoveNext
Loop

 

[RogerH]

I traded tripping over the 1st record, for tripping over the last record.

Private Sub Command42_Click()
Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim strbody As String
    Dim objMyTable As Object
    Dim strEmailAdds as string
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)
    Set objMyTable = CurrentDb.OpenRecordset("EmailTest")

objMyTable.MoveFirst

Do While Not objMyTable.EOF
     if  strEmailAdds = "" then  ' this might need isnull() , google it
       strEmailAdds =  objMyTable.email  
    else 
       strEmailAdds =strEmailAdds &";"  & objMyTable.email 
       objMyTable.MoveNext
     end if
LOOP

With OutMail
     .Body = "I hope this is working."
     .Subject = "Test"
     .To = strEmailAdds  ' or .BCC = strEmailAdds
     .Send
End With


Set objMyTable = Nothing
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

And dude. i'm done. you'll have to debug the rest of this yourself.

Gasman: THAT would put an extra ; at the end of the string.

 

[Gasman]

Gasman: THAT would put an extra ; at the end of the string.

Yes it does, but makes no difference to sending the email. I tried it before posting.

I've seen similar code where the people take off the last character in situations like that, so easily worked around.

I do not know for sure what would be in the email field when EOF is set, but I suspect it would be the last record data, which in this case would be added twice.?

That last comment applies to the previous version of the code as I see you have now placed the MoveNext to possibly be the last statement in the do loop.

 

[RogerH]

That last comment applies to the previous version of the code as I see you have now placed the MoveNext to possibly be the last statement in the do loop.

you're right, the .movenext should be after End if

 

[pld60]

RogerH and Gasman,

Thank you so much for your help, I have it working. With this

Private Sub Command42_Click()
Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim strbody As String
    Dim objMyTable As Object
    Dim strEmailAdds As String
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)
    Set objMyTable = CurrentDb.OpenRecordset("EmailTest")
objMyTable.MoveFirst
Do While Not objMyTable.EOF
   strEmailAdds = strEmailAdds & objMyTable.email & ";"
   objMyTable.MoveNext
Loop
 
 
With OutMail
     .Body = "I hope this is working."
 
     .Subject = "Test"
     .BCC = strEmailAdds
 
     .Send
 
End With
Set objMyTable = Nothing
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Now I have an interesting problem with trying to send from a specific outlook account.

This code works,

Private Sub Command41_Click()
 
Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim strbody As String
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)
On Error Resume Next
    With OutMail
 
 
    .Body = "I hope this works."
 
    .Subject = "Test"
    .To = "myemailaddress"
    .SendUsingAccount = OutApp.Session.Accounts.Item(2)
    .Send
 
 
End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
  DoCmd.Close
End Sub

This does not,

Private Sub Command42_Click()
Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim strbody As String
    Dim objMyTable As Object
    Dim strEmailAdds As String
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)
    Set objMyTable = CurrentDb.OpenRecordset("EmailTest")
objMyTable.MoveFirst
Do While Not objMyTable.EOF
   strEmailAdds = strEmailAdds & objMyTable.email & ";"
   objMyTable.MoveNext
Loop
 
 
With OutMail
     .Body = "I hope this works."
 
     .Subject = "Test"
     .BCC = strEmailAdds
     .SendUsingAccount = OutApp.Session.Accounts.Item(2)
     .Send
 
End With
Set objMyTable = Nothing
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

So, adding this line,

.SendUsingAccount = OutApp.Session.Accounts.Item(2)

seems to cause the 2nd to fail although there is no error, just no email sent.
The multiple emails works if sent with just .Send
SendusingAccount works when sent to one specified email address.
Ideas

 

[Gasman]

If you put a breakpoint on the .send line what does the account show?

When I created my code I was using Outlook 2003 and it had no .SendUsing property, so I used a template to get the same result.
I now have 2007 and have just tried the property on my 4th account and it assigns and works fine.

Are you using an exchange server, as I have seen posts stating that all mailboxes appear to be one account when using Exchange Server?

http://www.experts-exchange.com/que...orking-code-not-finding-Mailbox-Accounts.html

 

[pld60]

Gasman,

I think my problem is with the exchange server. I am working with IT to help me out with this, but the guy I'm dealing with does not seem to know. I will post when I have a solution.