By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,253 Members | 1,690 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,253 IT Pros & Developers. It's quick & easy.

Wanting to give users 3 options

P: n/a
All,
I have a procedure which checks the users Outlook Inbox for the
existance of an email from a specific address. If one is found, a question
is asked to the user asking if they wish to allow the database to save the
attachment and import the data. The reason for this is that the network is
extremely slow and the import takes several minutes to complete. During the
import, the database looks as though it is not responding. Giving them the
option to cancel the import stops them being interrupted whilst working.

My problem is that I wish to give the users another option which if they
select it, will import however many mails exist in their Inbox without
prompting them each time it finds a specific mail. This would be the case if
they do not intend to use the database for some time.

I'm a Newbie at VBA so forgive me if it's messy :o(

My code is as follows:

Public Sub CheckInbox(SavePath As String, StatusPart As Integer)
On Error GoTo AutoImportError
Dim mliNew As MailItem
Dim Ns As NameSpace
Dim oOutlook As Outlook.Application
Dim QuestionAuto As String

Set oOutlook = New Outlook.Application
Set Ns = oOutlook.GetNamespace("MAPI")
Set mfrInbox = Ns.GetDefaultFolder(olFolderInbox)
With mfrInbox
Forms!frm_switchboard!txt_InboxCount = .Items.Count
DoEvents

If .Items.Count = 0 Then 'No mails in Inbox
Forms!frm_switchboard!txt_InboxCount = .Items.Count
Exit Sub 'Quit the routine if no mails exist
Else
.Items.Sort "Received", True
'Go through every email in the Inbox
For Each mliNew In .Items

If mliNew.SenderName = "AS400 George" Then 'Mail is one
we're looking for

'A mail is found, check with the user if they want to import it!!!!
QuestionAuto = MsgBox("Intake Manager has found an email in your Inbox which
it needs to import!" & vbNewLine & _
"The database will try now attempt to import the file" & vbCrLf
& vbCrLf & "This may cause the database to stop responding for several
minutes." & vbNewLine & _
"Do you want it to continue?", vbYesNo + vbQuestion, "Import
Check")

If QuestionAuto = vbNo Then
ImportTimer = 0 'Reset the timer
Exit Sub
End If
mliNew.Attachments.Item(1).SaveAsFile SavePath 'Save the
attachment
ImportRatio

If Not IsNull(DLookup("psku", "tbl_tmp_ratio")) Then
MsgBox "During the Auto Import proceedure, a ratio
sku was found in the text file" & vbCrLf & _
"that doesn't exist in the database!" & vbCrLf & _
"Please update the ratio sku information now!!" &
vbCrLf & vbCrLf & _
"The database will attempt the import again within
the hour", vbCritical, "New Ratio Sku"

DoCmd.SetWarnings False
DoCmd.RunSQL "delete * from tbl_tmp_ratio;" 'Clear the temp ratio
table
DoCmd.SetWarnings True

Exit Sub
End If

ImportNewDay 'Run the Import proceedure
mliNew.Delete 'Delete the mail

End If
Next 'Move onto next mail in Inbox

End If
End With

Main_Exit:
Set mliNew = Nothing
Set Ns = Nothing
Set oOutlook = Nothing
Exit Sub

AutoImportError:
MsgBox "An error has occurred with the following details:" & vbNewLine &
_
"Description: " & Err.Description & vbNewLine & _
"Error Number: " & Err.Number & vbNewLine & vbNewLine & _
"Please report these details to the database administrator"
Resume Main_Exit
End Sub
Dec 10 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
>>...will import however many mails exist in their Inbox without
prompting them each time it finds a specific mail. .


if QuestionAuto <> vbyes then QuestionAuto = MsgBox("Intake Manager has
found an email in your Inbox ...

Once they say yes, it won't ask again.

Dec 10 '05 #2

P: n/a
I maybe missing the point here but wont the message box show again as it
exists within the FOR loop? It needs to exist here ( I believe) as I only
want it show if a mail from a specific sender exists within the Inbox!!!

Many thanks,

Mark
"Pachydermitis" <pr*******@gmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...
...will import however many mails exist in their Inbox without
prompting them each time it finds a specific mail. .


if QuestionAuto <> vbyes then QuestionAuto = MsgBox("Intake Manager has
found an email in your Inbox ...

Once they say yes, it won't ask again.

Dec 10 '05 #3

P: n/a

"Mark" <ma*********@ntlworld.com> wrote in message
news:mR*****************@newsfe3-gui.ntli.net...
I maybe missing the point here but wont the message box show again as it
exists within the FOR loop? It needs to exist here ( I believe) as I only
want it show if a mail from a specific sender exists within the Inbox!!!

Many thanks,

Mark
With that code, if the user selects Yes, then the msgbox would not show
again. Note, that it tests for QuestionAuto before executing. Once the users
presses Yes, QuestionAuto will remain equal to vbYes.

If this isn't the behavior that you wanted, I think you need to explain more
clearly exactly what you want the message box to do.

I'm guessing that what you want is 3 choices: Import Yes, Import No, Import
All. But that's just a guess.

Randy
"Pachydermitis" <pr*******@gmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...
...will import however many mails exist in their Inbox without
prompting them each time it finds a specific mail. .


if QuestionAuto <> vbyes then QuestionAuto = MsgBox("Intake Manager has
found an email in your Inbox ...

Once they say yes, it won't ask again.



Dec 10 '05 #4

P: n/a
Your assumption is correct in as much as I would like the 3 choices you
describe. What I am getting is a message on the screen every time a mail is
found from "AS400 George".

Apologies for the vague descriptions,
Mark

"Randy Harris" <ra***@SpamFree.com> wrote in message
news:ta*******************@newssvr29.news.prodigy. net...

"Mark" <ma*********@ntlworld.com> wrote in message
news:mR*****************@newsfe3-gui.ntli.net...
I maybe missing the point here but wont the message box show again as it
exists within the FOR loop? It needs to exist here ( I believe) as I only
want it show if a mail from a specific sender exists within the Inbox!!!

Many thanks,

Mark


With that code, if the user selects Yes, then the msgbox would not show
again. Note, that it tests for QuestionAuto before executing. Once the
users
presses Yes, QuestionAuto will remain equal to vbYes.

If this isn't the behavior that you wanted, I think you need to explain
more
clearly exactly what you want the message box to do.

I'm guessing that what you want is 3 choices: Import Yes, Import No,
Import
All. But that's just a guess.

Randy
"Pachydermitis" <pr*******@gmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...
>>>...will import however many mails exist in their Inbox without
>>>prompting them each time it finds a specific mail. .
>
> if QuestionAuto <> vbyes then QuestionAuto = MsgBox("Intake Manager has
> found an email in your Inbox ...
>
> Once they say yes, it won't ask again.
>


Dec 11 '05 #5

P: n/a

"Mark" <ma*********@ntlworld.com> wrote in message
news:ow*******************@newsfe4-win.ntli.net...
Your assumption is correct in as much as I would like the 3 choices you
describe. What I am getting is a message on the screen every time a mail is found from "AS400 George".

Apologies for the vague descriptions,
Mark


Mark, if I understand what you need, the message box just isn't going to be
adequate.

I think what you have is this. The user has a number of messages and is
given the option to import each one. If I've got this right, you really
need 4 choices. Something along the lines of:

Yes
No
Yes to All
No to All (or Exit)

For an effective UI you really need the fourth one. Suppose the user is
going through a long list and wishes to stop. He would then need to choose
No for each message or choose Yes to All, which might not be want he wants.

You should create a simple form with the four command buttons. You can
easily make this look like a message box, but with all of the buttons you
need. If you wish, you can make the form modal, like a msgbox, so that the
user must make a selection to continue.

To get the Yes to All effect, you can simply set a public variable, then
have the message scan code skip the prompt when that variable gets set. We
can help you with the code. Don't be intimidated.

Randy

Dec 12 '05 #6

P: n/a
Hi Randy,
I think I understand where you are going with this. Rather than ask for
help from the offset, I'd like to have a go with researching this before I
start as "Public Variables" are a new concept for me. Please keep an eye on
this thread as I'll no doubt need some help with it.

Thanks for your time,

Mark

"Mark" <ma*********@ntlworld.com> wrote in message
news:ow*******************@newsfe4-win.ntli.net...
Your assumption is correct in as much as I would like the 3 choices you
describe. What I am getting is a message on the screen every time a mail
is found from "AS400 George".

Apologies for the vague descriptions,
Mark

"Randy Harris" <ra***@SpamFree.com> wrote in message
news:ta*******************@newssvr29.news.prodigy. net...

"Mark" <ma*********@ntlworld.com> wrote in message
news:mR*****************@newsfe3-gui.ntli.net...
I maybe missing the point here but wont the message box show again as it
exists within the FOR loop? It needs to exist here ( I believe) as I
only
want it show if a mail from a specific sender exists within the Inbox!!!

Many thanks,

Mark


With that code, if the user selects Yes, then the msgbox would not show
again. Note, that it tests for QuestionAuto before executing. Once the
users
presses Yes, QuestionAuto will remain equal to vbYes.

If this isn't the behavior that you wanted, I think you need to explain
more
clearly exactly what you want the message box to do.

I'm guessing that what you want is 3 choices: Import Yes, Import No,
Import
All. But that's just a guess.

Randy
"Pachydermitis" <pr*******@gmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...
>>>...will import however many mails exist in their Inbox without
>>>prompting them each time it finds a specific mail. .
>
> if QuestionAuto <> vbyes then QuestionAuto = MsgBox("Intake Manager
> has
> found an email in your Inbox ...
>
> Once they say yes, it won't ask again.
>


Dec 12 '05 #7

P: n/a

"Mark" <ma*********@ntlworld.com> wrote in message
news:9E****************@newsfe3-gui.ntli.net...
Hi Randy,
I think I understand where you are going with this. Rather than ask for help from the offset, I'd like to have a go with researching this before I
start as "Public Variables" are a new concept for me. Please keep an eye on this thread as I'll no doubt need some help with it.

Thanks for your time,

Mark


I'll be here as will lots of others.

Good Luck,
Randy

Dec 12 '05 #8

P: n/a
HI Randy,
I managed to acheive what I wanted but not probably as you imagined. As
you suggested, I created a form to mimic a MsgBox with my custom buttons.
Once the CheckInbox sub was called and an email found, the custom MsgBox was
opened. This form contained a hidden text box which had a default value of
null. By clicking on any of the buttons, the text box was populated wih a
one, all or none.
When the sub opened the form, it checked to see if the value was null, if so
exited the sub.

By clicking on any of the buttons, the sub was called again. Now with a
'variable' in the form, I could import one, all or none!!!

Many thanks for pointing me in the right direction :o)

Public Sub CheckInbox(SavePath As String, StatusPart As Integer)
On Error GoTo AutoImportError
Dim mliNew As MailItem
Dim Ns As NameSpace
Dim oOutlook As Outlook.Application
Set oOutlook = New Outlook.Application
Set Ns = oOutlook.GetNamespace("MAPI")
Set mfrInbox = Ns.GetDefaultFolder(olFolderInbox)
With mfrInbox
Forms!frm_switchboard!txt_InboxCount = .Items.Count
DoEvents

If .Items.Count = 0 Then 'No mails in Inbox
Forms!frm_switchboard!txt_InboxCount = .Items.Count
Exit Sub 'Quit the routine if no mails exist
End If
.Items.Sort "SenderName", True
'Go through every email in the Inbox

For Each mliNew In .Items

If mliNew.SenderName = "AS400 George" Then 'Mail is one we're looking for

'If the MsgBox is open, it needs to be invisible
If SysCmd(acSysCmdGetObjectState, acForm, "frm_ImpAlert") = 1 Then
DoCmd.OpenForm "frm_ImpAlert", acNormal, , , , acHidden
Else
DoCmd.OpenForm "frm_ImpAlert", acNormal, , , , acWindowNormal
End If
DoEvents

If Forms!frm_ImpAlert!Txt_ImpValue = "None" Then 'They don't want to
import to exit code
DoCmd.Close acForm, "frm_ImpAlert"
Exit Sub
ElseIf IsNull(Forms!frm_ImpAlert!Txt_ImpValue) Then ' No selection has
been made
Exit Sub
End If
mliNew.Attachments.Item(1).SaveAsFile SavePath 'Save the attachment

ImportRatio 'Colleact ratio items sub

If Not IsNull(DLookup("psku", "tbl_tmp_ratio")) Then
DoCmd.Close acForm, "frm_ImpAlert"
DoEvents
MsgBox "During the Auto Import proceedure, a ratio sku was found in the
text file" & vbCrLf & _
"that doesn't exist in the database!" & vbCrLf & _
"Please update the ratio sku information now!!" & vbCrLf & vbCrLf & _
"The database will attempt the import again within the hour",
vbCritical, "New Ratio Sku"

DoCmd.SetWarnings False
DoCmd.RunSQL "delete * from tbl_tmp_ratio;" 'Clear the temp ratio
table
DoCmd.SetWarnings True
Exit Sub
End If
ImportNewDay 'Run the Import proceedure
mliNew.Delete 'Delete the mail

If Forms!frm_ImpAlert!Txt_ImpValue = "One" Then
DoCmd.Close acForm, "frm_ImpAlert"
Exit Sub
End If
End If

Next 'Move onto next mail in Inbox

End With

DoCmd.Close acForm, "frm_ImpAlert"
Main_Exit:
Set mliNew = Nothing
Set Ns = Nothing
Set oOutlook = Nothing
Exit Sub

AutoImportError:
MsgBox "An error has occurred with the following details:" & vbNewLine &
_
"Description: " & Err.Description & vbNewLine & _
"Error Number: " & Err.Number & vbNewLine & vbNewLine & _
"Please report these details to the database administrator"
Resume Main_Exit
End Sub
"Mark" <ma*********@ntlworld.com> wrote in message
news:9E****************@newsfe3-gui.ntli.net...
Hi Randy,
I think I understand where you are going with this. Rather than ask for
help from the offset, I'd like to have a go with researching this before I
start as "Public Variables" are a new concept for me. Please keep an eye
on
this thread as I'll no doubt need some help with it.

Thanks for your time,

Mark

"Mark" <ma*********@ntlworld.com> wrote in message
news:ow*******************@newsfe4-win.ntli.net...
Your assumption is correct in as much as I would like the 3 choices you
describe. What I am getting is a message on the screen every time a mail
is found from "AS400 George".

Apologies for the vague descriptions,
Mark

"Randy Harris" <ra***@SpamFree.com> wrote in message
news:ta*******************@newssvr29.news.prodigy. net...

"Mark" <ma*********@ntlworld.com> wrote in message
news:mR*****************@newsfe3-gui.ntli.net...
I maybe missing the point here but wont the message box show again as
it
exists within the FOR loop? It needs to exist here ( I believe) as I
only
want it show if a mail from a specific sender exists within the
Inbox!!!

Many thanks,

Mark

With that code, if the user selects Yes, then the msgbox would not show
again. Note, that it tests for QuestionAuto before executing. Once the
users
presses Yes, QuestionAuto will remain equal to vbYes.

If this isn't the behavior that you wanted, I think you need to explain
more
clearly exactly what you want the message box to do.

I'm guessing that what you want is 3 choices: Import Yes, Import No,
Import
All. But that's just a guess.

Randy

"Pachydermitis" <pr*******@gmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...
>>>...will import however many mails exist in their Inbox without
>>>prompting them each time it finds a specific mail. .
>
> if QuestionAuto <> vbyes then QuestionAuto = MsgBox("Intake Manager
> has
> found an email in your Inbox ...
>
> Once they say yes, it won't ask again.
>



Dec 16 '05 #9

P: n/a
Thanks for posting back the results Mark. I'm glad it worked out.

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

Dec 16 '05 #10

P: n/a
"Mark" <ma*********@ntlworld.com> wrote in message
news:D_***************@newsfe2-win.ntli.net...
HI Randy,
I managed to acheive what I wanted but not probably as you imagined. As you suggested, I created a form to mimic a MsgBox with my custom buttons.
Once the CheckInbox sub was called and an email found, the custom MsgBox was opened. This form contained a hidden text box which had a default value of
...truncated...


I meant to chime in on this when I first read the post, but forgot. I use an
easy but less elegant method. I use the following.
Fred Zuckerman

Dim I as String
I = Inputbox("Enter your choice" & vbCrLf & _
"1 = Option A" & vbCrLf & _
"2 = Option B" & vbCrLf & _
"3 = Option C")
Select Case I
Case "1"
'do stuff for option 1
Case "2"
'do stuff for option 2
Case "3"
'do stuff for option 3
Case Else
'invalid selection
exit sub
End Select


Dec 16 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.