Question Looping in vba code

mari_hitz

Registered User.
Local time
Yesterday, 19:20
Joined
Nov 12, 2010
Messages
120
Hi everybody!

I had an issue: I wanted to send personalized e-mails to a group of people indicating how much money they had to refund to my company.
I have a table which includes First Name, E-mail, Manager and Amount to reufund.
I have managed to create a code which send an e-mail to this persons and the body with the message personalized, however, it sends the mail to each people but does not matchs name with e-mail and the amount to refunds. It appears always the first name and amount that is on the table.

Do you have any idea on can I do to match this information? I have read in internet that I should do a looping to indicate to go to the next line.
Here's my code:

Code:
Private Sub Command41_Click()
Dim MyDB As Database
  Dim MyRS As Recordset
  Dim MyTable 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
  Dim TheBody As String
  
 
  Set MyDB = CurrentDb
  Set MyRS = MyDB.OpenRecordset("Sheet2")
  MyRS.MoveNext
  
  
  
  ' Create the Outlook session.
  Set objOutlook = CreateObject("Outlook.Application")
  
  Do Until MyRS.EOF
  ' Create the e-mail message.
  Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
  TheAddress = [Enterprise]
  TheBody = "Dear " & [First] & "," & vbNewLine & vbNewLine & _
          "You have to refund " & [Debe] & " to the company. Please review your situation." & vbNewLine & vbNewLine & _
          "Greetings"
  
     With objOutlookMsg
        ' Add the To recipients to the e-mail message.
        Set objOutlookRecip = .Recipients.Add(TheAddress)
        objOutlookRecip.Type = olBCC
        
        ' Set the Subject, the Body, and the Importance of the e-mail message.
        .Subject = "Action Required: Please review assignment and/or MyTimeandExpenses information"
        .Body = TheBody
        .Importance = olImportanceHigh  'High importance
         

        
        
    
     
        ' 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
   Set MyTable = Nothing
End Sub
 
You're getting data from the form instead of the recordset. You want:

MyRS!Enterprise
 
Thanks pbaldy!

You are always here to help me.
I have changed this part of the code:
Code:
TheAddress = [Enterprise]
to
Code:
TheAddress = MyRS![Enterprise]

However, it continues to do the same, it sends the e-mail to the different people, however the wording of the body brings information for the person located in the first row.
 
Any tought? Should I change another part of the code?
 
Thanks!
 
That was just an example. Any time you want a value from the recordset you have to use that syntax.
 
You are awsome! Thank you very much for everything!

Can I make another quick question? I am using office 2010, which was updated in my PC two days ago. I used to use this code with Office 2007 and Outlook used to send my e-mails directly, now it asks me for a permission.
How I disable that?

Thanks!
 
You are composing the body outside of the loop so it never changes. I also don't think you should be controlling the loop by using Outlook recipients. The loop should be controlled by your table that contains the information regarding who owes money.
 
You are composing the body outside of the loop so it never changes.

The body is built within the loop. The problem is with:

TheBody = "Dear " & [First] & ...

[First] is coming from the form (presumably), not the recordset. OP's response seems to indicate referring to the recordset solved the issue.
 
The indentation got me. I didn't notice the outer loop.
 
You all have been great to me!
Thanks again Pbaldy for all your help to me and thanks to Pat Too!

This issue has been resolved
 

Users who are viewing this thread

Back
Top Bottom