Working with New Email event in Outlook using VBA

[Irene]

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]

Hi Irene,

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

 

[Irene]

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.

 

[Mel Arquiza]

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

 

[Pedro CR]

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

 

[Irene]

Thank you so much, Irene

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