Consulting

Results 1 to 3 of 3

Thread: Looping through x-amount of orders - Adding data to an email for each order

  1. #1

    Arrow Looping through x-amount of orders - Adding data to an email for each order

    Hi.

    I am learning VBA, and are working on a project that I can use for my work.
    For reference - We work in a program based on IBM Reflection Software, so some commands will be from the Reflection for IBM 14.1 Object Library.

    My question here though focuses on the Outlook part of the code.

    I would like to create a macro that runs through x-amount of orders, writing specified info to an email from each order.

    Below is the code I have written that does above for 1 order.
    Is it possible to make a loop that runs the code, adds the text from the variables to the email, and goes through the loop again, using the same variables.
    Again the focus here is on writing to the email.

    Sub MailLoading1()


    Dim CollF1 As Variant
    Dim CollF2 As Variant
    Dim CollF3 As Variant
    Dim CollF4 As Variant
    Dim txt As Variant
    Dim OutlookApp As Outlook.Application
    Dim OutlookMail As Outlook.MailItem

    With Session
    CollF1 = .GetDisplayText(6, 16, 30)
    CollF2 = .GetDisplayText(6, 51, 30)
    CollF3 = .GetDisplayText(7, 16, 30)
    CollF4 = .GetDisplayText(7, 51, 30)
    End With


    Set OutlookApp = New Outlook.Application
    Set OutlookMail = OutlookApp.CreateItem(olMailItem)

    With OutlookMail

    .BodyFormat = olFormatHTML
    .HTMLBody = CollF1 & "<br>" & CollF2 & "<br>" & CollF3 & "<br>" & CollF4
    .Display
    End With

    Set OutlookApp = Nothing
    Set OutlookMail = Nothing
    End Sub

  2. #2
    So after further experimenting, I am considering creating a Class Module, that will take the info from the variables and create a object.
    Then using x-amount of objects to write the email.

    Am I way off here - Is it even possible to do like this?

    A different solution would be to include Excel.
    Run the loop, write the data in one row, run the loop again and write the data to the next row.
    Then creating the email from the worksheet.
    This might be the best solution.

  3. #3
    Look at http://www.vbaexpress.com/forum/show...Subject-Header to see the best way to start Outlook and write to a message body.

    Without knowing anything about your process, or the IBM software, you would create your loop around the 'Session' group to assemble a string which you can write to the message body. With the suggested code you would use something like the following to assemble the values from your loop

    strBodyText = strBody Text & vbcr & CollF1 & vbcr & CollF2 & vbcr & CollF3 & vbcr & CollF4

    If you can get your data into Excel, you might be better using https://www.gmayor.com/ManyToOne.htm to merge to e-mail.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •