Rabu, 26 Oktober 2011

Re: [MS_AccessPros] My first Do Loop but with questions

 

John,

Thank you, that was the problem. I am trying to learn the loops in vba and this was my first.

 
Jim Wagner
________________________________

________________________________
From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Wednesday, October 26, 2011 6:55 AM
Subject: RE: [MS_AccessPros] My first Do Loop but with questions

 
Jim-

You've established a new With, so you have to qualify the field with the recordset name:

.Subject = "Finance Report Test for " & myset![First Name]

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Jim Wagner
Sent: Wednesday, October 26, 2011 3:40 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] My first Do Loop but with questions

Crystal,

This is what I have so far. I eventually want to add the code to email from gmail, but I have been using the outlook at work to send reports. I will eventually add the gmail code.

' code from crystal
' code from crystal
' code from crystal
' code from crystal
Private Sub Command281_Click()
Dim mydb As DAO.Database
Dim myset As DAO.Recordset
Dim MyAddress As String

Set mydb = CurrentDb
Set myset = mydb.OpenRecordset("SELECT * FROM tblTodaysBirthdays where [E-mail Address] is not null")

'~~~~~~~~~~~~~~~~~~~~~
' more statements

With myset
Do While Not .EOF
MyAddress = Nz(![E-mail Address], "")
'MsgBox "My name and address" & " " & MyAddress

'new code
Dim objol As New Outlook.Application
Dim objmail As MailItem
Set objol = New Outlook.Application
Set objmail = objol.CreateItem(olMailItem)
With objmail
.To = "luvmymelody@yahoo.com"
.Subject = "Finance Report Test for " & ![First Name]

.Body = "Here is the Finance Report"

.NoAging = True
'.Attachments.Add ("\\Afwfs\AF\Users\jwganer\My Documents\Weekly Reports\JC11001.snp")
.Display
End With
Set objmail = Nothing
Set objol = Nothing
SendKeys "%{s}", True

'new code

' original crystal code
' MsgBox "My name and address is " _
' & ![First Name] & (" " + ![Last Name]) & (" " + ![E-mail Address])
' original crystal code

.MoveNext
Loop
End With

'cleanup code
On Error Resume Next
If Not myset Is Nothing Then
myset.Close
Set myset = Nothing
End If
Set db = Nothing

End Sub
' code from crystal
' code from crystal
' code from crystal
' code from crystal

Jim Wagner
________________________________

________________________________
From: Crystal <strive4peace2008@yahoo.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Tuesday, October 25, 2011 8:00 PM
Subject: Re: [MS_AccessPros] My first Do Loop but with questions

hi Jim,

.Subject = "Finance Report Test for " & ! [First Name]

.Subject is a member of the email message object

! [First Name] is a member if the recordset object

only one of them would be valid at a time -- depends on With

please post the whole procedure

Warm Regards,
Crystal

*
(: have an awesome day :)
*

________________________________
From: Jim Wagner

Crystal,

I have another question. Like I mentioned earlier, I am trying to set up the email portion. I am slowly building so I understand it and not just copying code and say it is done. I want to internalize it.

My next question is I have inserted the code to email through outlook. It works great except it is using the subject line with that first record again in the contact list.

my line of code looks like below. I used your code to see if it would work. Like I said it sends an email to the count of records in the table but uses only the name in the wrong table. what am i doing wrong?

.Subject = "Finance Report Test for " & ! [First Name]

Jim Wagner
________________________________

________________________________
From: Crystal

you're welcome, Jim :)
... and I see Bill answered your followup question :)

Warm Regards,
Crystal

*
(: have an awesome day :)
*

________________________________
From: Jim Wagner

Crystal,

Your code worked like a charm.
But I have a follow up question. I noticed that even after compiling I cannot use F8 to step into the code. I just hear a beep. Do I have something wrong?

Jim Wagner
________________________________

________________________________
From: Crystal

hi Jim,

> " I saw that hyphen and thought why would they use that."

the Access template follows names created by the Outlook structure ... so no thought about good practice went into the names. It makes transferring data easier between Access and Outlook when the names are the same since they don't have to be mapped. Personally, I use good names and map if I need to exchange data.

I, along with others, have talked to Microsoft about the poor naming in the templates -- they should set better examples, especially for Access. If the templates accepted VBA code, you'd see some good examples but since they submissions are limited to macros, it is hard to make a worthy one.

Warm Regards,
Crystal

*
(: have an awesome day :)
*

________________________________
From: Jim Wagner

Crystal,

I am using the template from MS that is for contacts. I saw that hyphen and thought why would they use that. I am using the database to conform to what I want to do. So I am learning new things in it. The do loop is way over due to learn.

I will add your code and try it and then let you know. I am eventually going to add some code that emails a birthday card that is a report saved as a pdf to those in the table each day. I found some code that emails through gmail.

Thank You

Jim Wagner
________________________________

________________________________
From: Crystal <

Hi Jim,

you need to specify where to get [First Name] and [Last Name] as you did with myset![E-mail Address]

to make things more efficient, I surrounded your corrected code with With...End With. I also utilized + in case Last Name or E-mail Address is not filled out. Also added cleanup code

you should avoid using special characters and spaces in names. Underscore _ is ok.

'~~~~~~~~~~~~~~~~~~~~~
' more statements

With myset
Do Until myset.EOF

MyAddress = nz(![E-mail Address],"")
MsgBox "My name and address" & " " & MyAddress

MsgBox "My name and address is " _
& ![First Name] & (" " + ![Last Name] ) & (" " + ![E-mail Address])

.MoveNext
Loop
End With

'cleanup code
on error resume next
if not myset is nothing then
myset.close
set myset = nothing
end if
set db = nothing

End Sub
'~~~~~~~~~~~~~~~~~~~~~

instead of MsgBox, try using Debug.Print

when you are running the code, you can look at the Immediate (debug) window instead of pressing OK all the time with a MsgBox

press Ctrl-G to Goto the debuG window

Warm Regards,
Crystal

*
(: have an awesome day :)
*

________________________________
From: luvmymelody

Hello all,

I am practicing my first do loop and questioning why it took me so long to learn this. But here I am with a question.

I have the below code that I am trying to just get a message box to show first, last and address in the msgbox. If I use the first msgbox in the code it works great. The email addresses show up in the msgbox and all is great. But when I try to use the second msgbox in the code, I get the first name from the Contacts table and the email addresses from the tblTodaysBirthdays table cycle through with the same name. But the person's name in the msgbox is from the main table not the table the select statement is based on.

I have several tables involved in the question. The contacts table and the tblTodaysBirthdays. The latter is a table made from a make table query built every day based on the Contacts table.

Private Sub Command281_Click()
Dim mydb As DAO.Database
Dim myset As DAO.Recordset
Dim MyAddress As String

Set mydb = CurrentDb
Set myset = mydb.OpenRecordset("SELECT * FROM tblTodaysBirthdays where [E-mail Address] is not null")

Do Until myset.EOF

MyAddress = myset![E-mail Address]
'MsgBox "My name and address" & " " & MyAddress
MsgBox "My name and address is" & " " & [First Name] & " " & [Last Name] & " " & MyAddress
myset.MoveNext

Loop
End Sub

What am I missing here?

Thanks for the help

Jim Wagner

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]

__._,_.___
Recent Activity:
MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

.

__,_._,___

Tidak ada komentar:

Posting Komentar