Do Loop to build Dynamic Table and insert into HTML body

  • Thread starter Thread starter Nathan Bell
  • Start date Start date
N

Nathan Bell

All,

I have a problem and have included my copy of code for reference. I
need to build an excel table (with dynamic number of rows to be determined
by a do loop counter (# of users) and it should build a table, and insert in
data that is concatenated from other variables up above in the code
(intUserCt and txtQID).

I know that I need to open a Do loop and set a counter variable and then the
usercount should be = to teh number of times that the loop will run. I
don't know how to write the data into excel on a row by row basis and
concatenating teh fields into excel. From there I want to set the table to
a variable and do another HTMLBody = Replace on %Users% if it will work.
The end result table sample is shown below with the code. Any assistance or
pointers would be greatly appreciated.

Cell1 Cell2

txtQID+"USER"+intCounter txtTempPassword
1234567USER1 Use1Time

Sub Prime_Online()



Dim myOLApp As New Outlook.Application

Dim myOLItem As Outlook.MailItem

Dim txtToAddress As String

Dim txtOrderNumber As String

Dim txtAgency As String

Dim txtQID As String

Dim txtTrackingNumber As String

Dim txtDecryptionKey As String

Dim txtProdKey As String

Dim txtDLCode As String

Dim intUserCt As Integer





txtToAddress = InputBox("Please insert in a To: email address", "To:")

txtQID = InputBox("Please insert in the customer id.", "Customer ID")

txtProdKey = InputBox("Please insert in the Product Key.", "Product Key")

intUserCt = InputBox("Please insert # of Users.", "Number of Users")

txtDLCode = InputBox("Please insert in the Download Code.", "Download Code")







Set myOLItem = myOLApp.CreateItemFromTemplate("C:\Documents and
Settings\user\Application Data\Microsoft\Templates\Prime Online
Welcome.oft")



With myOLItem

..SentOnBehalfOfName = "Purchasing"

..To = Trim(txtToAddress)

'.BCC = "CLL - AFW-Online Data Center"

..Subject = "Prime Online Order"

..HTMLBody = Replace(myOLItem.HTMLBody, "%QID%", txtQID)

..HTMLBody = Replace(myOLItem.HTMLBody, "%ProdKey%", txtProdKey)

..HTMLBody = Replace(myOLItem.HTMLBody, "%DLCode%", txtDLCode)

..HTMLBody = Replace(myOLItem.HTMLBody, "%UserCt%", intUserCt)

..HTMLBody = Replace(myOLItem.HTMLBody, "%Users%", intUserCt)

End With



myOLItem.Display



End Sub
 
You may use the Offset function in Excel.

Set a Range variable to the upper left cell you want to start with, e.g.:

Set rn=Cells("a1")

Now do the loop, and for a new row call:

Set rn=rn.Offset(1,0)

For every column use a For Next loop, and
write the value of column 1 to rn.Value, the second to
rn.Offset(0,col).Value, etc.

That works but is pretty slowly. A lot faster is:. create an array with all
the values you want to export, and set it to the range at once. As I
remember there's a limitation of 256 characters per cell when using this
method.

--
Best regards
Michael Bauer - MVP Outlook

: VBOffice Reporter for Data Analysis & Reporting
: Outlook Categories? Category Manager Is Your Tool
: <http://www.vboffice.net/product.html?pub=6&lang=en>


Am Tue, 30 Sep 2008 12:37:11 -0500 schrieb Nathan Bell:
 
Back
Top