Outlook sendmail macro

**Safecracker** · 07-21-2009, 10:27 AM

I am hoping one of the resident experts can assist me (or point me in the right direction) with what will likely be a simple macro.

I would like to create a macro that will pull up a very basic userform, where one could enter a number upon receipt of a package. The macro would then send one of five people (based upon the range of the number, eg. 1000-1999 address #1, 2000-29999 address #2) an email, stating their package has arrived.

I have not yet created a macro for outlook, however have a bit of experience with excel macros.

Any assistance would be greatly appreciated!

Btw the outlook version is 2000.

Thanks in advance!

**DonkeyOte** · 07-21-2009, 02:46 PM

For general guidance on mailing from XL see the links in my sig.

How far have you progressed with your User Form code ?

**Safecracker** · 07-21-2009, 04:08 PM

Thanks for the response!

I have not progressed very far at all, I have very limited knowledge of User Forms in outlook, I did take a look at your site and blog, (very nice site btw) however I am still at square 1.

I am far more comfortable working within excel, however I am unable to use excel for this project.

I am not certain what I am attempting to do is even possible from with outlook. From what I have learned thus far, User Forms in outlook are part of an email message.

What I am hoping to do, is have the user press a toolbar button that will bring up a user form (excel type form), and enter a four digit number into the form and click submit.

Upon submission the code should determine where to send a email based upon the four digit number (1000-1999 send to email address #1, 2000-2999 send to address #2 etc...) and then send the email without any further actions by the user.

Is this possible from within outlook (2000) ?

**DonkeyOte** · 07-22-2009, 04:00 AM

First off two points...

1 - I failed to notice this was in the OL forum so apologies - my refs to XL were a little pointless
2 - I wish they were my own sites - they are not !

OK, without knowing just how complex your real requirements are you *may* be able to do this just using standard InputBox approach ... ie creating a macro which invokes an InputBox which in turn creates an email etc...

Code:

Public Sub Package_Receipt()
Dim vPackage As Variant, lngPackage As Long, strPackage As String, strRecip As String, olMI As MailItem
vPackage = InputBox("Enter Package Number", "Package No.", "0000")
If Not IsNumeric(vPackage) Then
    MsgBox "Invalid Package Number Entered - Please Try Again", vbCritical, "Invalid"
    Exit Sub
End If
lngPackage = CLng(vPackage)
strPackage = Format(lngPackage, "0000")
Select Case lngPackage
    Case 1000 To 1999
        strRecip = "address1@somewhere.com"
    Case 2000 To 2999
        strRecip = "address2@somewhere.com"
    Case 3000 To 4999
        strRecip = "address3@somewhere.com"
    Case 5000 To 9999
        strRecip = "address4@somewhere.com"
    Case Else
        MsgBox "No Mail to be Issued for this Package Number", vbInformation, "No Mail Issued"
        Exit Sub
End Select
Set olMI = CreateItem(olMailItem)
With olMI
    .Subject = "Package " & strPackage & " Received"
    .Body = "Here is the email re: package " & strPackage
    .To = strRecip
    .Send 'change to Save to have as draft or .Display to physically display message dialog in OL
End With
Set olMI = Nothing
End Sub

Unfortunately I don't have access to OL 2000 but you should find you can then create a "button" on the OL toolbar to invoke the above routine on click
(in latter versions you would go to View - Toolbars - Customise - on Commands Tab select Macros - pick the Macro of interest and drag to wherever you want it placed (via Modify you can alter the text that appears - ie alter to "Run Package" etc...))

I hope that helps.

**darbid** · 07-22-2009, 05:18 AM

Originally Posted by Safecracker

What I am hoping to do, is have the user press a toolbar button that will bring up a user form (excel type form), and enter a four digit number into the form and click submit.

Upon submission the code should determine where to send a email based upon the four digit number (1000-1999 send to email address #1, 2000-2999 send to address #2 etc...) and then send the email without any further actions by the user.

Is this possible from within outlook (2000) ?

Sounds like it is.

I am not an "expert" I would call myself a forced learner of VBA for access and outlook - I have limited experience to none in excel.

I had a couple of questions;

1. Is this just for you or are you wanting to distribute this to people?
2. Where are all these four digit numbers stored and the email addresses stored?
3. Do you have access to VB6(visual studio)
4. Does "send an email" mean the currently selected email or is this email being generated by your excel programming.

**Safecracker** · 07-22-2009, 10:17 AM

DonkeyOte, thank you very much for your help! Thanks to Darbid as well!

Your code was exactly what I was looking for!

Problem solved!

This place rocks!