VBA Help - Iam new

[gg_yyc]

I would like to send an E-mail by selecting a Range of a particular Row within a excel sheet (Multiple Rows in a Sheet; every row is a different action item) And after the selecting the row the script should be able to place cells in the E-mail as described in the E-mail body below.

This is what E-mail Body would look like:

Hello <Name>:

Please provide a status update for the action item below:

Request Number: (First Cell of the Selected Range, E.g. in Range A2:F2, This should populate value for A2)

Description: (Second Cell of the Selected Range, E.g. in Range A2:F2, This should populate value for B2)

Related MI Details: (Third Cell of the Selected Range, E.g. in Range A2:F2, This should populate value for C2)

Who: (Fourth Cell of the Selected Range, E.g. in Range A2:F2, This should populate value for D2)

What: (Fifth Cell of the Selected Range, E.g. in Range A2:F2, This should populate value for E2)

When: (Sixth Cell of the Selected Range, E.g. in Range A2:F2, This should populate value for F2)

The criteria for closing an action item are:

· Action item/recommendation has been resolved

· Action item/recommendation is being tracked operationally or as a project

· Action item/recommendation has been deemed to be not cost effective or not implementable


If you need more information regarding the MI in question you can find the final report for the MI on the IT Service Management site. MI Final reports are sorted by date of the MI.

Thank you

 

[Harpreet Singh]

Hi, if i am correctly understand , you can try this Code. i attached the Sample_Sheet(Send Email) too.

Sub Send_Mail()
Dim Outlk As Object
Dim Nmail As Object
Dim rng As Range

Set Outlk = CreateObject("Outlook.application")
Set Nmail = Outlk.Createitem(0)
Set rng = Application.Selection

If rng.Columns.Count < 6 Then

MsgBox "Please select a valid range to send Email."

Exit Sub

End If

On Error Resume Next

With Nmail
.to = ""
.cc = ""
.Bcc = ""
.Subject = "Please provide a status update."
.Body = "Hello Sir/Madam," & Chr(10) & Chr(10) & _
        "Please provide a status update for the action item below:" & Chr(10) & Chr(10) & _
        "Request Number:- " & Selection.Cells(1, 1).Value & Chr(10) & _
        "Descripition:- " & Selection.Cells(1, 2).Value & Chr(10) & _
        "Related MI Details:- " & Selection.Cells(1, 3).Value & Chr(10) & _
        "Who:- " & Selection.Cells(1, 4).Value & Chr(10) & _
        "What:- " & Selection.Cells(1, 5).Value & Chr(10) & _
        "When:- " & Selection.Cells(1, 6).Value & Chr(10) & Chr(10) & _
        "The criteria for closing an action item are:-" & Chr(10) & _
        "· Action item/recommendation has been resolved." & Chr(10) & _
        "· Action item/recommendation is being tracked operationally or as a project" & Chr(10) & _
        "· Action item/recommendation has been deemed to be not cost effective or not implementable" & Chr(10) & Chr(10) & _
        "If you need more information regarding the MI in question you can find the final report for the MI on the IT Service Management site. MI Final reports are sorted by date of the MI." & Chr(10) & Chr(10) & _
        "Thank You."
       
End With

Nmail.display
On Error GoTo 0

Set Nmail = Nothing
Set Outlk = Nothing


End Sub

for more clarification on this please upload a sample file.