Subir
01-29-2014, 08:07 AM
I am trying to create a macro in Excel which will reply atomically on open or selected outlook item and also that item will be color wise categorized .
I am using below mention code in Outlook. but I want this to be done form Excel.
Is it possible, Kindly help me.
I am using Office 2007 and later version.
___________
Sub InsertNameInReply()
Dim Msg As Outlook.MailItem
Dim MsgReply As Outlook.MailItem
Dim strGreetName As String
Dim lGreetType As Long
Dim objCategory As Category
' set reference to open/selected mail item On Error Resume Next
Select Case TypeName(Application.ActiveWindow)
Case "Explorer"
Set Msg = ActiveExplorer.Selection.Item(1)
Case "Inspector"
Set Msg = ActiveInspector.CurrentItem
Case Else
End Select
On Error GoTo 0
If Msg Is Nothing Then GoTo ExitProc
' figure out greeting line On Error Resume Next
lGreetType = InputBox("How to greet:" & vbCr & vbCr & "Type '1' for name, '2' for time of day")
On Error GoTo 0
If lGreetType = False Then GoTo ExitProc
If lGreetType = 1 Then
strGreetName = Left$(Msg.SenderName, InStr(1, Msg.SenderName, " ") - 1)
ElseIf lGreetType = 2 Then
Select Case Time
Case Is < 0.5
strGreetName = "Good morning"
Case 0.5 To 0.75
strGreetName = "Good afternoon"
Case Else
strGreetName = "Good evening"
End Select
Else ' something else entered?? Goto ExitProc
End If
Set MsgReply = Msg.Reply
With MsgReply
.Subject = "RE:" & Msg.Subject
.CC = Msg.CC
.HTMLBody = "<span style=""font-family : verdana;font-size : 10pt""><p>Hello " & strGreetName & ",</p></span>" & .HTMLBody
.Display
End With
Msg.Categories = "Green Category"
'Msg.Category.Color = "Green"
'Msg.Category = olCategoryColorBlue
Msg.Save
ExitProc:
Set Msg = Nothing
Set MsgReply = Nothing
End Sub
I am using below mention code in Outlook. but I want this to be done form Excel.
Is it possible, Kindly help me.
I am using Office 2007 and later version.
___________
Sub InsertNameInReply()
Dim Msg As Outlook.MailItem
Dim MsgReply As Outlook.MailItem
Dim strGreetName As String
Dim lGreetType As Long
Dim objCategory As Category
' set reference to open/selected mail item On Error Resume Next
Select Case TypeName(Application.ActiveWindow)
Case "Explorer"
Set Msg = ActiveExplorer.Selection.Item(1)
Case "Inspector"
Set Msg = ActiveInspector.CurrentItem
Case Else
End Select
On Error GoTo 0
If Msg Is Nothing Then GoTo ExitProc
' figure out greeting line On Error Resume Next
lGreetType = InputBox("How to greet:" & vbCr & vbCr & "Type '1' for name, '2' for time of day")
On Error GoTo 0
If lGreetType = False Then GoTo ExitProc
If lGreetType = 1 Then
strGreetName = Left$(Msg.SenderName, InStr(1, Msg.SenderName, " ") - 1)
ElseIf lGreetType = 2 Then
Select Case Time
Case Is < 0.5
strGreetName = "Good morning"
Case 0.5 To 0.75
strGreetName = "Good afternoon"
Case Else
strGreetName = "Good evening"
End Select
Else ' something else entered?? Goto ExitProc
End If
Set MsgReply = Msg.Reply
With MsgReply
.Subject = "RE:" & Msg.Subject
.CC = Msg.CC
.HTMLBody = "<span style=""font-family : verdana;font-size : 10pt""><p>Hello " & strGreetName & ",</p></span>" & .HTMLBody
.Display
End With
Msg.Categories = "Green Category"
'Msg.Category.Color = "Green"
'Msg.Category = olCategoryColorBlue
Msg.Save
ExitProc:
Set Msg = Nothing
Set MsgReply = Nothing
End Sub