Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Outlook / Programming VBA / September 2005

Tip: Looking for answers? Try searching our database.

Working with New Email event in Outlook using VBA

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Irene - 27 Sep 2005 00:06 GMT
Hi,

I found a couple of sample codes that I put together and everything is
working fine.
How can I access the new mail subject the same way the code access the new
mail recipient. I have tried mewitem.subject but it is returning a subject
from an old email.

Sub application_NewMail()
  Dim oEmail As Object
  Dim myInbox As MAPIFolder
   Dim newItem As Object
   Dim myRecips As Recipients
   Dim myRecip As Recipient
   
Set myInbox = Application.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
   totalitems = myInbox.Items.Count

   Set newItem = myInbox.Items.Item(1)
   Set myRecips = newItem.Recipients
 
   Set myRecip = myRecips.Item(1)

   
   'MsgBox myRecip.Name
 
  Set oEmail = Application.CreateItem(olMailItem)
  oEmail.Subject = myRecip.Name
  'oEmail.Body = newItem.Subject
  oEmail.Recipients.Add "Reminder"
  oEmail.Send

 
    Set myInbox = Nothing
   Set newItem = Nothing
   Set myRecips = Nothing
   Set myRecip = Nothing
End Sub


Thank you
Irene  
Mel Arquiza - 27 Sep 2005 09:36 GMT
Hi Irene,

Try to change the the oEmail.Send to "oEmail.display" see if this works and
run the code again.

> Hi,
>
[quoted text clipped - 38 lines]
> Thank you
> Irene  
Irene - 27 Sep 2005 13:20 GMT
Hi Mel,
thank you for your fast responds.
I am sorry that I did not explain everything I am trying to do.
    I want to send a txt reminder to my cell phone hence oEmail.Recipients.Add
"Reminder" (this has my cell email) every time I receive an email. That part
works fine. I receive a notification that I have received an email. What I
want to do is display the new mail’s subject and who it is from in my txt
message.Does that make sense.

> Hi Irene,
>
[quoted text clipped - 43 lines]
> > Thank you
> > Irene  
Mel Arquiza - 28 Sep 2005 07:29 GMT
Hi Irene,

You can try this if this works for you. What the code do is convert the
phone number to text and vice versa. Try vba code it in excel and go back to
outlook when it works.

Place this code to a module and select a cell and type some numbers or
letters.

Sub DoPhone_NumbertoText()
   Dim rngSrc As Range
   Dim lMax As Long, lCtr As Long
   Dim J As Integer
   Dim Phone As String, Letter As String

   Set rngSrc = ActiveSheet.Range(ActiveWindow.Selection.Address)
   lMax = rngSrc.Cells.Count

   For lCtr = 1 To lMax
       If Not rngSrc.Cells(lCtr).HasFormula Then
           Phone = rngSrc.Cells(lCtr).Value
           For J = 1 To Len(Phone)
               Letter = UCase(Mid(Phone, J, 1))
               Select Case Letter
                   Case "1" To "5"
                       Letter = Chr(97)
                   Case "6"
                       Letter = Chr(65)     'May want to change
                   Case "7" To "9"
                       Letter = Chr(62)
                   Case "0"
                       Letter = Chr(37)    'May want to change
               End Select
               Mid(Phone, J, 1) = Letter
           Next J
           rngSrc.Cells(lCtr).Value = Letter
       End If
   Next lCtr
End Sub

Sub DoPhone_TexttoNumber()
   Dim rngSrc As Range
   Dim lMax As Long, lCtr As Long
   Dim J As Integer
   Dim Phone As String, Digit As String

   Set rngSrc = ActiveSheet.Range(ActiveWindow.Selection.Address)
   lMax = rngSrc.Cells.Count

   For lCtr = 1 To lMax
       If Not rngSrc.Cells(lCtr).HasFormula Then
           Phone = rngSrc.Cells(lCtr).Value
           For J = 1 To Len(Phone)
               Digit = UCase(Mid(Phone, J, 1))
               Select Case Digit
                   Case "A" To "P"
                       Digit = Chr((Asc(Digit) + 1) \ 3 + 28)
                   Case "Q"
                       Digit = "7"     'May want to change
                   Case "R" To "Y"
                       Digit = Chr(Asc(Digit) \ 3 + 28)
                   Case "Z"
                       Digit = "9"     'May want to change
               End Select
               Mid(Phone, J, 1) = Digit
           Next J
           rngSrc.Cells(lCtr).Value = Phone
       End If
   Next lCtr
End Sub

> Hi Mel,
> thank you for your fast responds.
[quoted text clipped - 52 lines]
> > > Thank you
> > > Irene  
Pedro CR - 29 Sep 2005 02:25 GMT
hi

As I understood you problem is getting the subject for the new email, since
sometimes you get an old subject.
I have had a similar problem before and this is because doing
myRecips.Item(1) does not guarantee that you are getting the New or most
recent email message (I can assure you that). There is also the problem that
if you receive multiple messages at a time, the event might not fire once
for each message. Instead it might fire randomly, ie after every couple of
messages. This is because Outlook is receiving email on one thread and
processing VBA on another and they are not syncronized.

So if you are using Outlook 2003, you should process the NewMailEx event
instead of NewMail, because this event receives the new MailItem as an
argument. This you way you are assured to always be processing each new mail
message, because the MailItem taht is passed into the function points to
each new mail item that you receive. It's guaranteed.

If you running a version prior to OL2003, you might not have the NewMailEx
event so you might consider doing this to work around the problem:
- inspect all the messages in Inbox everytime the event is fired.
- All the messages that are not flagged are assumed to be New, so they are
sent to your pager and after that, are also flagged to mark them as sent.
- All messages that were already flagged were already sent so they are
skipped.
(Don't forget to call the Save method on each message after setting the flag
on a messagebecause otherwise the flag is lost most of the times).

Bellow you will find sample code that does exactly what I explained to you
before. It should work for all version of Outlook. If you use OL2003, you
can use the NewMailEx event instead which is somewhat more efficient if you
have a large number of itmes in your inbox.

Private Sub Application_NewMail()
   Dim mi As MailItem, oEmail As MailItem
   Dim f As MAPIFolder
   Set f = Application.Session.GetDefaultFolder(olFolderInbox)

   For Each mi In f
       If mi.FlagStatus = olFlagMarked And mi.FlagIcon = olGreenFlagIcon
Then
           ' skip. message already sent
       Else
           Set oEmail = Application.CreateItem(olMailItem)
           oEmail.Subject = myRecip.Name
           'oEmail.Body = newItem.Subject
           oEmail.Recipients.Add "Reminder"
           oEmail.Send

           mi.FlagStatus = olFlagMarked
           mi.FlagIcon = olGreenFlagIcon
           mi.Save
       End If
   Next

   Set oEmail = Nothing
   Set mi = Nothing
   Set f = Nothing

End Sub

Hope this works
Pedro

> Hi Irene,
>
[quoted text clipped - 129 lines]
>> > > Thank you
>> > > Irene
Irene - 29 Sep 2005 02:33 GMT
Thank you so much, Irene

> hi
>
[quoted text clipped - 193 lines]
> >> > > Thank you
> >> > > Irene
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.