Read email addresses from subform and creat a new mail

[Mister-B]

Hi there,

I can't seem to find an answer to this problem anywhere:

I have a main form "Daten" with a subform "BewerberUF". The sub form is formatted as a table and contains 11 columns. The column titled "NachVor" contains the surname and the first name of customers divieded by a comma (eg. Smith, John). How can I loop through the records in the subform, extract the data in the fields "NachVor", open Outlook, create a new message and insert all the data from NachVor into the "From" Field on the new mail?

[ranman256]

'collect all the emails in the subform
sub btnSendEmails_click()
dim vTo

with me.subform.form.recordset
    .movefirst
    while not .eof
        vTo = vTo & ";"
       .movenext
    wend

     'send email
   email1 vTo, "subject mater" , "body of email"
end sub


Public Function Email1(ByVal pvTo, ByVal pvSubj, ByVal pvBody) As Boolean
Dim oApp As Outlook.Application
Dim oMail As Outlook.MailItem
'-------
'YOU MUST ADD THE OUTLOOK APP IN REFERENCES!!!   checkmark MICROSOFT OUTLOOK OBJECT LIBRARY in the vbE menu, Tools, References
'-------

On Error GoTo ErrMail

Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(olMailItem)

With oMail
    .To = pvTo
    .Subject = pvSubj
    .Body = pvBody
    
    .Send
End With
Email1=true

Set oMail = Nothing
Set oApp = Nothing
Exit Function


ErrMail:
MsgBox Err.Description, vbCritical, Err
Resume Next
End Function

[Mister-B]

Thanks for the quick help. Unfortunately I am getting the following error at the word "subform" in the line with me.subform.form.recordset

Compile Error - Method or Data member not Found

What am I doing wrong?

[pbaldy]

Ranman appears to be offline. Replace "subform" with the name of your subform control ("BewerberUF" ?). The name of the control on the main form, not the name of the subform itself if they're different.

[Mister-B]

I can now get the Code to run through. I can see that it is running through the subform table but it is not returning any values. If I Output the read values to a message box I just get a whole line of semi colons. Access Returns an error saying that no recipients were specified. Any ideas?

[pbaldy]

Ranman forgot the email address. Try adding:

vTo = vTo & !EmailAddressFieldName & ";"

[Mister-B]

That is fantastic. Thank you so much

[pbaldy]

No problem. Ranman did all the heavy lifting.