macro to reply sometimes fails - why?

[Mark]

Hi

I've had help previously with this one from this forum (with great
appreciation)

I've finally come up with the below macro. It works ok sometimes, but
fails on occasions. So far I cannot track down why. Any clues?

Many thanks
Mark



Sub Hi_Name()
Dim myItem As Outlook.MailItem
Dim NewMsg As Outlook.MailItem

' get valid ref to current item
On Error Resume Next
Select Case TypeName(Application.ActiveWindow)
Case "Explorer"
Set myItem = ActiveExplorer.Selection.Item(1)
myItem.Display
Case "Inspector"
Set myItem = ActiveInspector.CurrentItem
Case Else
End Select
On Error GoTo 0

If myItem Is Nothing Then
MsgBox "Could not use current item. Please select or open a
single email.", _
vbInformation
GoTo ExitProc

End If

Set NewMsg = myItem.Reply

With NewMsg

NewMsg.BodyFormat = olFormatHTML


..HTMLBody = "<span style=""font-size:11.0pt;font-family:
Arial;color:#1F497D""><p>Regards, Mark" & vbCr & vbCr & vbCr & vbCr &
"</p>" & .HTMLBody

..HTMLBody = "<span style=""font-size:11.0pt;font-family:
Arial;color:#1F497D""><p> " & "</p><br />" & .HTMLBody

..HTMLBody = "<span style=""font-family : Arial;font-size :
11pt;color:#1F497D""><p>Hi " & Left$(myItem.SenderName, InStr(1,
myItem.SenderName, " ") - 1) & ",</p></span>" & .HTMLBody


End With

myItem.Close olDiscard

NewMsg.Display


ExitProc:
Set myItem = Nothing
Set NewMsg = Nothing

End Sub

 

[Ken Slovak]

What's failing? Try commenting out the error handler or adding multiple
places in the code where you test for the Err condition to see what's
failing when.

 

[Mark]

Thanks Ken

Sorry, yes I should have been more precise. The error just occurred
again when I tried to run it. It says "Run-time error '5': Invalid
procedure call or argument"

When I click Debug it highlights the line:

..HTMLBody = "<span style=""font-family : Arial;font-size :
11pt;color:#1F497D""><p>Hi " & Left$(myItem.SenderName, InStr(1,
myItem.SenderName, " ") - 1) & ",</p></span>" & .HTMLBody

But it does not happen every time. I have not been able to diagnose
why it happens sometimes and not others.

Thanks
Mark

 

[Ken Slovak]

An intermittent error is the hardest to find of course

I would break things out a bit just to make it easier to debug. Put in a new
line that gets the myItem.SenderName as a string value and use that string
variable in the HTML string. That way you'll know if that's causing the
problem.

Take a look at those items where it happens and try to see what's different
about them. One thing that might help is a MAPI viewer, where you can
examine the raw properties of items where the macro fails. MFCMAPI is a free
download from MS, OutlookSpy is what I use for that
(www.dimastr.com/outspy).

That can be a big help with problems like this.




Thanks Ken

Sorry, yes I should have been more precise. The error just occurred
again when I tried to run it. It says "Run-time error '5': Invalid
procedure call or argument"

When I click Debug it highlights the line:

..HTMLBody = "<span style=""font-family : Arial;font-size :
11pt;color:#1F497D""><p>Hi " & Left$(myItem.SenderName, InStr(1,
myItem.SenderName, " ") - 1) & ",</p></span>" & .HTMLBody

But it does not happen every time. I have not been able to diagnose
why it happens sometimes and not others.

Thanks
Mark

 

[Michael Bednarek]

Sorry, yes I should have been more precise. The error just occurred
again when I tried to run it. It says "Run-time error '5': Invalid
procedure call or argument"

When I click Debug it highlights the line:

.HTMLBody = "<span style=""font-family : Arial;font-size :
11pt;color:#1F497D""><p>Hi " & Left$(myItem.SenderName, InStr(1,
myItem.SenderName, " ") - 1) & ",</p></span>" & .HTMLBody

But it does not happen every time. I have not been able to diagnose
why it happens sometimes and not others.

The obvious suspect for that error message is the call to Left(). What
if .SenderName contains no " "? Then Left() gets called
Left(.SenderName, -1) which will produce the error message you quoted.

 

[Mark]

Thanks Michael

Yes, that seems to be it. If i edit it to be the following it works

..HTMLBody = "<span style=""font-family : Arial;font-size :
11pt;color:#1F497D""><p>Hi " & (myItem.SenderName) & ",</p></span>"
& .HTMLBody

But, it does not look so good.

Cheers
Mark

 

[Mark]

Hi all

Can anybody suggest an if statement that would. I have limited
knowledge of Outlook VBA.

But along the lines of the below.

Thanks
Mark

If myItem.SenderName contains " " Then
GoTo FirstName:

..HTMLBody = "<span style=""font-family : Arial;font-size :
11pt;color:#1F497D""><p>Hi " & Left$(myItem.SenderName, InStr(1,
myItem.SenderName, "") - 1) & ",</p></span>" & .HTMLBody

End If

FirstName:
..HTMLBody = "<span style=""font-family : Arial;font-size :
11pt;color:#1F497D""><p>Hi " & Left$(myItem.SenderName, InStr(1,
myItem.SenderName, " ") - 1) & ",</p></span>" & .HTMLBody

 

[Michael Bednarek]

Hi all

Can anybody suggest an if statement that would. I have limited
knowledge of Outlook VBA.

There's nothing specific to Outlook VBA about any of this.

But along the lines of the below.

Thanks
Mark

If myItem.SenderName contains " " Then
GoTo FirstName:

.HTMLBody = "<span style=""font-family : Arial;font-size :
11pt;color:#1F497D""><p>Hi " & Left$(myItem.SenderName, InStr(1,
myItem.SenderName, "") - 1) & ",</p></span>" & .HTMLBody

End If

FirstName:
.HTMLBody = "<span style=""font-family : Arial;font-size :
11pt;color:#1F497D""><p>Hi " & Left$(myItem.SenderName, InStr(1,
myItem.SenderName, " ") - 1) & ",</p></span>" & .HTMLBody

At least you should simplify the code above to
If ... Then
...
Else
...
End If

or you could use the Iff() function:
.HTMLBody = ... & Iif(InStr(1, myItem.SenderName, " ") > 0, Left$(myItem.SenderName, InStr(1, myItem.SenderName, " ") - 1), myItem.SenderName) & ...
(untested)
or you could break the .SenderName at the at-sign ("@").

It's probably more efficient to extract whatever you decide
from .Sendername into a variable using a dedicated code section,
and then using that variable in the construction of .HTMLBody.

 

[Mark]

Many thanks Michael

Appreciate your help

I'll give it a go.

Mark

 

[Mark]

Thanks all for your help.

For the sake of anyone else who wants to play with it, the following
is what I came up with and it works for me, but no guarantees for
anybody else.

I have put buttons on my task bar. Actually I have a few: Dear,
Dear_reply_all, Hi, Hello, Many thanks. All have differing salutations
etc.

Dear_reply_all uses Set NewMsg = myItem.ReplyAll


-----------------------

Sub Dear_Name_if()
Dim myItem As Outlook.MailItem
Dim NewMsg As Outlook.MailItem

' get valid ref to current item
On Error Resume Next
Select Case TypeName(Application.ActiveWindow)
Case "Explorer"
Set myItem = ActiveExplorer.Selection.Item(1)
myItem.Display
Case "Inspector"
Set myItem = ActiveInspector.CurrentItem
Case Else
End Select
On Error GoTo 0

If myItem Is Nothing Then
MsgBox "Could not use current item. Please select or open a
single email.", _
vbInformation
GoTo ExitProc

End If

Set NewMsg = myItem.Reply

With NewMsg

NewMsg.BodyFormat = olFormatHTML


..HTMLBody = "<span style=""font-size:11.0pt;font-family:
Arial;color:#1F497D""><p>Regards, Mark" & vbCr & vbCr & vbCr & vbCr &
"</p>" & .HTMLBody

..HTMLBody = "<span style=""font-size:11.0pt;font-family:
Arial;color:#1F497D""><p> " & "</p><br />" & .HTMLBody


If myItem.SenderName, " " Then

..HTMLBody = "<span style=""font-family : Arial;font-size :
11pt;color:#1F497D""><p>Dear " & Left$(myItem.SenderName, InStr(1,
myItem.SenderName, " ") - 1) & ",</p></span>" & .HTMLBody

Else
..HTMLBody = "<span style=""font-family : Arial;font-size :
11pt;color:#1F497D""><p>Dear " & Left$(myItem.SenderName, InStr(1,
myItem.SenderName, "@") - 1) & ",</p></span>" & .HTMLBody

End If


End With

myItem.Close olDiscard

NewMsg.Display


ExitProc:
Set myItem = Nothing
Set NewMsg = Nothing

End Sub