Need help Fine Tuning my Outlook Object Code

[noodnutt]

Hi Guys

I almost have this working to my liking, Almost!

I have the following statement:

If Me.snfStatus.Value = 2 Then
Set objOutlook = CreateObject("Outlook.Application")
Else
Cancel = True
End If

Problem.1
It is creating mail for those outside the above criteria

Problem.2
I have it focused around the query that the continuous form is attached to, which is ok I suppose if you like an instances of the email button on each record within the recordset.

Problem.3
It is creating 2 email instances of the same message.

...............................................................................

Ideally what I would love to happen is:

On the Main form: frmPlanner have the [Email Button] - When Clicked

1. The code would then look at table [tblTMS]

2. Look to see if the criteria of snfStatus = 2

3. Use the email values in the field hypEmail
(But! If hypEmail = N/A meaning there is no address available for this customer so move onto the next record)

4. Not a Big deal, but would really like it if once the email has been generated the recordset updates the snfStatus = 3

..............................................................................

Here is the code in it's entirety; as I stated, it looks at qryDC_H_E (which I included the hypEmail field and don't mind using, just need to get it ironed out a little) rather than the underlying table .

Private Sub btnEmail_Click()

Dim MyDB As Database
Dim MyRS 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

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("qryDC_H_E")
MyRS.MoveFirst

' Create the Outlook session.
If Me.snfStatus = 2 Then
Set objOutlook = CreateObject("Outlook.Application")
Else
Cancel = True
End If

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![hypEmail]

With objOutlookMsg
' Add the To recipients to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo


Dim myRoute As Object
Set myRoute = Me.txfRoute

Dim myLoadID As Object
Set myLoadID = Me.txfLoadID
Dim myPO As Object
Set myPO = Me.txfPONumber
Dim myStacks As Object
Set myStacks = Me.snfStacks
Dim myDC As Object
Set myDC = Me.txfDC
Dim myDay As String
myDay = Format(Me.dtfLoadDate, "Dddd")
Dim myDate As String
myDate = Format(Me.dtfLoadDate, "dd-Mmm-yy")
Dim myTime As Object
Set myTime = Me.dtfVendArr

Dim mySig As String


Dim mySubject As String
mySubject = "Confirming Load ID: " & myLoadID & " will be collected by " & myRoute

Dim myBody As String
myBody = "Hi" & vbCrLf & _
"" & vbCrLf & _
"On " & myDay & ", " & myDate & " At " & myTime & vbCrLf & _
"" & vbCrLf & _
"We will be collecting the following Order(s)on behalf of Acme" & vbCrLf & _
"" & vbCrLf & _
"Load ID: " & myLoadID & vbCrLf & _
"P.O. (s): " & myPO & vbCrLf & _
"Stack(s): " & myStacks & vbCrLf & _
"" & vbCrLf & _
"Bound for: " & myDC & vbCrLf & _
"" & vbCrLf & _
"NOTE: We strive to meet all expected Pick up Arrival times provided although" & vbCrLf & _
"infrequent events and circumstance outside our control may affect the Pick up Time" & vbCrLf & _
"" & vbCrLf & _
"Should you have any issues or concerns on the morning of the pick up please contact the Fleet Controller" & vbCrLf & _
"( As early as possible to avoid potential inconveniences )" & vbCrLf & _
"" & vbCrLf & _
"Regards, " & vbCrLf & _
"Transport"

' Set the Subject, the Body, and the Importance of the e-mail message.
.subject = mySubject
.Body = myBody

' Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Display
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing

End Sub

[noodnutt]

And being that it's 3.40am Sunday morning I very rudely forgot to thank you all in advance for your pending thoughts and comments.

Cheers
Mick.

[Ron Paii]

"noodnutt" <nood...@gmail.com> wrote in message
news:e0fbcd9d-fe84-4e4b...@googlegroups.com...

> Hi Guys
>
> I almost have this working to my liking, Almost!
>
> I have the following statement:
>
> If Me.snfStatus.Value = 2 Then
> Set objOutlook = CreateObject("Outlook.Application")
> Else
> Cancel = True

Problem.1 exit the sub after setting cancel
exit sub

> End If
>
> Problem.1
> It is creating mail for those outside the above criteria
>
> Problem.2
> I have it focused around the query that the continuous form is attached
> to, which is ok I suppose if you like an instances of the email button on
> each record within the recordset.
>
> Problem.3
> It is creating 2 email instances of the same message.

does qryDC_H_E return 2 records for each address?

>
> ...............................................................................
>
> Ideally what I would love to happen is:
>
> On the Main form: frmPlanner have the [Email Button] - When Clicked
>
> 1. The code would then look at table [tblTMS]

If all the needed data is in the current record of the form try this
set myrs = me.recordsetclone
myrs.bookmark = me.bookmark
Instead of opening a query

[noodnutt]

Hi Ron

Thanks heaps for the pointer, it works well now.

If I can ask the following please.

As my Sub_Forms ( 20 of them ) are inserted into 20 individual pages of a Tab Control, and rather than having an email button for every record in the continuous view recordset, I was hoping to have just one button on the main form frmPlanner.

I tried the following, but it halted citing: Run-time error 2452
The expression you entered has an invalid reference to the Parent property.

Set MyDB = CurrentDb
Set MyRS = Parent!TabCtl1!RecordsetClone
MyRS.Bookmark = Me.Bookmark

Essentially what I was trying to achieve was to dynamically set myRS to the active Tab Control Page.

But, if it can't be done then I can live with the multiple email buttons.

Thanks again Ron.

Mick.

[Ron Paii]

"noodnutt" <nood...@gmail.com> wrote in message

news:c6223980-43ae-43fd...@googlegroups.com...

I don't use tabs much and have nothing to test, but try something like the
following.

set MyRS = Me!TabCtrl."SubFormCtrlName".Form.RecordsetClone

Otherwise get the current tab id from the tab control

set MyRS = Me."SubFormName".Form.RecordSetClone

[Ron Paii]

"noodnutt" <nood...@gmail.com> wrote in message
news:c6223980-43ae-43fd...@googlegroups.com...

An alternative is to write a global function and call it from the button on
the sub form.

ps. 20 tabs with 20 sub forms will be VERY slow opening and likely hard to
use. The speed issue can be helped by assigning the sub form source in a tab
event.

ps. Early binding to the Outlook object works well, until your users get a
different version of Outlook. Consider changing to late binding after you
have the form working.

[noodnutt]

Thanks for these suggestions Ron

Neither worked though, but I'm not stressing as I will just send each email individually once planning of it is completed.

Cheers
Mick.

[noodnutt]

And this would be the bit where I sit there with a blank der-expression.

Never been any good at constructing Global Functions, nor any clue regarding early / late bindings.

As for the form loading, you're most likely correct, especially once I throw this on the dinosaur treadmill that is our server it will probably have a hissy-fit and lock up on me.

But! this is all theory and playtime for me to try and convince Noah, that the ark can be built to store more animals in a smaller space and reduce the squillions of Excel workbooks into one central container.

Currently, just at my end of the process I generate a 5meg workbook each day that has to be kept, so multiply that by the 4 years I have been doing this and it's getting fat, then there's the other processed workbooks that take up to 2 - 3 meg each and the Michellen Man is looking anorexic by comparison.. :)

It will be up to the demi-gods to determine if they want to spend the green on a high-end professional programmer to do it properly, I just need to light a fire under their asses to motivate them..

Thanks heaps again.
Mick.

[Ron Paii]

"noodnutt" <nood...@gmail.com> wrote in message

news:262b8708-0fc2-4279...@googlegroups.com...

> And this would be the bit where I sit there with a blank der-expression.
>
> Never been any good at constructing Global Functions, nor any clue
> regarding early / late bindings.

early / late binding has to do with how you declared the Outlook objects.

Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment

To do this you must have set a reference to the Outlook library. If you
notice, that reference is to a specific version of outlook (in my case
"Microsoft Outlook 14.0 Object Library" which is office 2010). Someone with
a different version attempting to open your application will get a reference
error and the application will close. Removing the reference to Outlook
will allow anyone using any version of Outlook, or even without Outlook to
use your application. The problem for you is that you cannot declare your
Outlook objects as above.

Dim objOutlook As Object
Dim objOutlookMsg As Object
Dim objOutlookRecip As Object
Dim objOutlookAttach As Object

Above is late binding declaration, you lose intelligence making programming
harder. Also any Outlook constants will needed to be replaced. The rest of
the code remains unchanged. When programming, I will use early binding then
switch to late before final testing and release. I also declare all the
constants I use at the top of the module. You can get the value of each
constant from the object browser while Outlook is referenced.

>
> As for the form loading, you're most likely correct, especially once I
> throw this on the dinosaur treadmill that is our server it will probably
> have a hissy-fit and lock up on me.
>
> But! this is all theory and playtime for me to try and convince Noah, that
> the ark can be built to store more animals in a smaller space and reduce
> the squillions of Excel workbooks into one central container.
>
> Currently, just at my end of the process I generate a 5meg workbook each
> day that has to be kept, so multiply that by the 4 years I have been doing
> this and it's getting fat, then there's the other processed workbooks that
> take up to 2 - 3 meg each and the Michellen Man is looking anorexic by
> comparison.. :)
>
> It will be up to the demi-gods to determine if they want to spend the
> green on a high-end professional programmer to do it properly, I just need
> to light a fire under their asses to motivate them..

You could keep the early binding; forcing all users to upgrade office to
match you version will may motivate them;)

>
> Thanks heaps again.
> Mick.
>

[Ron Paii]

"Ron Paii" <No...@none.com> wrote in message
news:knd5i9$nsd$1...@dont-email.me...

Here's how to a global function, otherwise you will need to have 20 version
of the email code, one for each button of each sub form. Note, there are
other ways to to do it, but I did not want to change your code to much.

Note I stripped down you existing event function and added a call to the new
Email_Click function, passing the open recordset

Private Sub btnEmail_Click()

'Dim MyDB As Database Not needed
Dim MyRS As Recordset
'Set MyDB = CurrentDb Not needed

Set MyRS = MyDB.OpenRecordset("qryDC_H_E")

Email_Click MyRS

set MyRS = Nothing ' Good ideal to cleanup

End Sub


' Note the new function does Not Reference any control. Create a new module
and copy it.

Private Sub Email_Click(MyRS 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

MyRS.MoveFirst

' Create the Outlook session.
If Me.snfStatus = 2 Then
Set objOutlook = CreateObject("Outlook.Application")
Else
Cancel = True

Exit Sub

[Ron Paii]

Private Sub Email_Click(MyRS as Recordset)

Change Private to public to make it available to other modules

Public Sub Email_Click(MyRS as Recordset)

[noodnutt]

Ron

Now that you have laid it out Binding in a simplistic term do you reckon I could use a precursory If Statement to check the version running then load the applicable statements. eg


If MSACCESS_Ver <= 12 then
Call Email_Ver_12
Else
Call Email_Ver_14
End If


Private Sub Email_Ver_12 ()

Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment

End Sub

Private Sub Email_Ver_14 ()

Dim objOutlook As Object
Dim objOutlookMsg As Object
Dim objOutlookRecip As Object
Dim objOutlookAttach As Object

End Sub

Also

That re-do on the Email Code is nice and clean and seems to execute quicker which is always nice.

Many thanks for all your helpful comments and assistance.

Will get this playtime prototype up and running a lot sooner.

But, for now, it's time to put this back in the box for at least a week as the Good Lady Wife and I are going on our first ever overseas holiday to some dreary little island called Phuket.. :)

Of course, I will be taking the trusty Notebook with me just in case I get struck down with Access withdrawal symptoms...lol..

Thanks heaps again Ron.

[Ron Paii]

"noodnutt" <nood...@gmail.com> wrote in message

news:9964d60c-143a-41d9...@googlegroups.com...

No, the reference is set in VBA not your code, and only 1 version can be
referenced. Which is why the application cannot be opened if any of the
references are missing.