Outlook body from Access

**danz** · 01-06-2006, 01:20 PM

I am looking to store information in Access and generate e-mails with contents of the DB. Being new to VBA this has been difficult and I have made lots of headway but Ive not hit a wall and stuck. The problem I?m having is looping the query in access. I can get the contents in from the first entry but that?s it. I?ve tried EOF and FOR and nada. Any ideas? I also want to put the data in a format that?s styled. Help!!!

[VBA]

Sub Net_Status()
MyDate = Date
Time = Now
NextHour = Hour(Time) + 1 & ":00:00"
DaNextHour = Format(NextHour, "hh:00 AMPM")
Set myolapp = CreateObject("Outlook.application")
Set myitem = myolapp.CreateItem(olMailItem)
Dim curMessage As Outlook.MailItem
Set curMessage = Application.ActiveInspector.CurrentItem
datasep = " - "

Dim rsOutages As ADODB.Recordset
Dim conOutages As ADODB.Connection
Dim strConnectionString As String
'Set the connection string and open the connection
Set conOutages = New ADODB.Connection
strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=H:\outages.mdb;" & _
"Persist Security Info=False"
conOutages.Open strConnectionString

'Attempt to retrieve task records from the database for the given job
Set rsOutages = New ADODB.Recordset
rsOutages.Open "select * from Outages where Include_ = True", _
conOutages, adOpenStatic, adLockReadOnly

Set myOlInspectors = Application.Inspectors

curMessage.SentOnBehalfOfName = "User 2"
curMessage.To = "User 1"
curMessage.CC = "User 3"
curMessage.Subject = "Network Status Summary " & MyDate & " " & DaNextHour & " PST"
curMessage.HTMLBody = "<HTML>" & Location & " " & Ticket & " " & TicStatus & "</html>"

Location = rsOutages.Fields("Location_")
Ticket = rsOutages.Fields("Ticket_")
TicStatus = rsOutages.Fields("TicStatus_")

'rsOutages.MoveFirst
'For Each Field In rsOutages
'If rsOutages.Fields("Include_") = "True" Then
'curMessage.HTMLBody = "<HTML>" & Location & " " & Ticket & " " & TicStatus & "</html>"
'Exit For
'End If
'Next

'Do While Not rsOutages.EOF
'For Each Field In rsOutages
'Do While rsOutages.Fields("Include_")
'If rsOutages.Fields("Include_") = "True" Then
'Debug.Print Location & " " & Ticket & " " & TicStatus
'curMessage.HTMLBody = "<HTML>" & Location & " " & Ticket & " " & TicStatus & "</html>"

'Next
'rsOutages.MoveNext
'Loop
'myitem.Send
End Sub

[/VBA]

**NateO** · 01-06-2006, 05:09 PM

Hello,

Assuming you know how to work the Outlook bind/stack your message body, I'll give you an example of iterating through a Recordset:

[vba]
Sub GrbAccessData()
Dim cn As Object, rs As Object
Const dbFullName As String = "c:\temp\db1.mdb"
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _
& dbFullName & ";"

Set rs = CreateObject("ADODB.Recordset")
With rs
.Source = "Select Field1, Field2, Field3 " & _
"From Table1 Where Table1.Field2 Like '%alb%'"
.Open , cn, 3, 3 '.Open , , adOpenStatic, adLockOptimistic
If Not .EOF Then
Do Until .EOF
Debug.Print .fields(0), .fields(1), .fields(2)
.MoveNext
Loop
Debug.Print
End If
.Close
End With
cn.Close
Set rs = Nothing: Set cn = Nothing
End Sub[/vba]
This is a late-bind approach from Excel, where I'm printing out the three fields, for each record in my immediate window, via ADO/OLE DB.

The key is the ADO MoveNext Method and to be mindful of the Recordset's EOF Property.

Clear as mud?

Edited by Nate Oliver:

Incidentally, this is not the format of my code in my module. The Open Method has a tab on the line-continuation, and I don't like to tab the entire code block as such.

I mention this with all due respect. But I'm a pretty picky guy when it comes to this stuff, and don't like my formatting to be messed with, I write code with the tabbing and whitespace the way I do for a reason. To self-document and highlight intent.

I like the idea of adding colour, but not so crazy about messing with my tabbing... I know, not my forum, constructive criticism... Is there an alternative for me?

Rant over, happy new year.

**danz** · 01-06-2006, 06:41 PM

Thanks for the quick response!

The code is clear and clean however I am not familiar with Outlook bind/stack but will do some searching and learn up on it. Would you have a link you could toss my way to get me going? I really appreciate your help.

Taking a look at my Immediate window I see your code is action

I now see that binding is what I will need to do to add the data in the body. Reading away. Confused as all hell.

**Bob Phillips** · 01-07-2006, 05:31 AM

Originally Posted by NateO

Incidentally, this is not the format of my code in my module. The Open Method has a tab on the line-continuation, and I don't like to tab the entire code block as such.

I mention this with all due respect. But I'm a pretty picky guy when it comes to this stuff, and don't like my formatting to be messed with, I write code with the tabbing and whitespace the way I do for a reason. To self-document and highlight intent.

I like the idea of adding colour, but not so crazy about messing with my tabbing... I know, not my forum, constructive criticism... Is there an alternative for me?

I think you are out of luck, if you use the VBA tags it formats as it formats. The rationale seems good, to ensure that the code at least gets some indenting (I hate non-indented code), but it has some quirks. I have a pet peeve where I start Dim statements in column 1 and it always moves them to column 4. Similalrly, when I do

'-----
Function ...
'------

it ends up as

'----
Function ...
<4 spaces> '----

looks ridiculous.

And it is even worse with multi-argument methods. I tend to use one line per argument, indented to line-up. The indenting gets removed.

**NateO** · 01-07-2006, 10:48 AM

Hi Dan,

You're welcome. I kind of wanted to isolate the recordset iteration so as not to muddy the waters, so now that we might be clear on that, let's look at putting it all together with another example. See the 2nd example here:

http://www.danielklann.com/excel/sen...s_the_body.htm

When I say bind, I'm assuming you're doing this from Access, and example #2 binds with Outlook on the late bind. And note how he's building the body. He's using Excel cells, but there's no reason you can't use tags similarly on the different records (when you use the MoveNext Method) or with fields in each record.

Hopefully this helps, if not, post back and perhaps I or another member can put it all together for you.

Thanks, XLD.

Well, right, I see the concept, and it might work for people who clearly don't apply reasonable formatting to their code... And it that case, yes, it's a bonus.

Call me anal-retentive if you want, I probably am. I'm a Finance manager and programmer of sorts, my work requires loads of finicky details... Either way, I really don't like my code getting shifted around like this... It almost makes me feel like I do not know what I'm doing when I look at my own code.

I cringe a little to think what it might do with something like this:

http://mrexcel.com/board2/viewtopic.php?p=901966#901966

Which uses a lot of tabbing/line-continuation... My temporary workaround might be to upload text files with my original source...

**danz** · 01-07-2006, 10:55 AM

Ok Im not sure how to proceed. When I run the code it now appears that is only show the last result. I assume I need to put it in some sort of array of some sorts. Any hints?

[VBA]
Sub Net_Status()
Dim cn As Object, rs As Object
Const dbFullName As String = "H:\TOC.mdb"
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _
& dbFullName & ";"
MyDate = Date
Time = Now
NextHour = Hour(Time) + 1 & ":00:00"
DaNextHour = Format(NextHour, "hh:00 AMPM")
Set myolapp = CreateObject("Outlook.application")
Set myitem = myolapp.CreateItem(olMailItem)
Dim curMessage As Outlook.MailItem
Set curMessage = Application.ActiveInspector.CurrentItem
datasep = " - "

Set rs = CreateObject("ADODB.Recordset")
With rs
.Source = "Select * " & _
"From Outages Where Outages.Include_ Like 'True'"
.Open , cn, 3, 3 '.Open , , adOpenStatic, adLockOptimistic
If Not .EOF Then
Do Until .EOF
'Debug.Print , .fields(3), .fields(4), .fields(5), .fields(6), .fields(7)
testing = .Fields(3) & .Fields(4) & .Fields(5) & .Fields(6) & .Fields(7)
.MoveNext
Loop
Debug.Print
End If
.Close
End With
cn.Close
Set rs = Nothing: Set cn = Nothing

curMessage.SentOnBehalfOfName = "User 2"
curMessage.To = "User 1"
curMessage.CC = "User 3"
curMessage.Subject = "Network Status Summary " & MyDate & " " & DaNextHour & " PST"
curMessage.HTMLBody = "<HTML>" & testing & "</html>"

End Sub
[/VBA]

**NateO** · 01-07-2006, 10:56 AM

Oh, what I also meant to add is that String concatenation as presented in the example can be fast on small strings, and it's very maintainable code. But concatenated over and over again on a large string is really bloody slow, much like using Redim Preserve or Union over and over again. You're forcing your computer to operate outside its cache, using very expensive algorithms....

Maintainable, but not worth maintaining. How's that for a paradox?

What I would be tempted to do if you have large Recordsets would be to stack a 1-d array, you know how many fields you have and how many records you have (RecordCount), so double-up your elements for your tags, start stacking, join the array using the Join() function, and pass that string in one go to the body.

Could be crazy enough to work... Good hunting.

**Ken Puls** · 01-07-2006, 10:57 AM

Originally Posted by NateO

Thanks, XLD.

Well, right, I see the concept, and it might work for people who clearly don't apply reasonable formatting to their code... And it that case, yes, it's a bonus.

Call me anal-retentive if you want, I probably am. I'm a Finance manager and programmer of sorts, my work requires loads of finicky details... Either way, I really don't like my code getting shifted around like this... It almost makes me feel like I do not know what I'm doing when I look at my own code.

I cringe a little to think what it might do with something like this:

http://mrexcel.com/board2/viewtopic.php?p=901966#901966

Which uses a lot of tabbing/line-continuation... My temporary workaround might be to upload text files with my original source...

Here's what it looks like, for the reference: http://vbaexpress.com/forum/showthre...4063#post54063

**NateO** · 01-07-2006, 10:58 AM

Hello,

Hopefully you posted this before seeing my example, from Dan's site. Give that a thorough review and see if that helps.

And I apologize if I'm being really negative about my code being shifted around, I'm normally a very positive person, and I do like the site, promise.

**danz** · 01-07-2006, 10:59 AM

Didnt see you post

Sorry for the re ask.

Im actully doing everything from Outlook. I wont have too many records (max 10-15) and they will vary from day to day.

**danz** · 01-07-2006, 11:33 AM

it work... damn your good

thanks for all your help. Now its time to get really crazy now that I see how this works. This VBA stuff is frustrating but its rewarding

**NateO** · 01-07-2006, 11:53 AM

You're welcome, glad to help.

So, hopefully you found that when you're stacking your string, you need to keep the old one and layer on, something like this:

[vba]
Sub foo()
Dim b() As Byte, i As Long
Dim newStr As String
Const tstStr As String = "foobar"
Let b = tstStr
For i = LBound(b) To UBound(b) Step 2
Let newStr = newStr & ChrW$(b(i))
Next
MsgBox newStr
End Sub[/vba]

What I find that helps, when you're building interfaces, is to break it down as we did in this thread. Get the Recordset iteration down. Get the string building down. Get the Outlook part down. And now, take the functional pieces and combobulate it all together.

It simplifies your goal sets, otherwise to try and slam it all it one go can be overwhelming...

And yes, it's a bit of an art and science with a pretty decent learning curve. Expect to fail at first but keep at it, the rewards and skills will come with time and effort.

Good hunting.

Thread: Outlook body from Access

Thread Tools

Display

Outlook body from Access

Posting Permissions