Hi,
I'm not used to VBA, but is learning slowly. Could someone help to input comment in the VBA also?
PROBLEM:
As below image, I want VBA that will email user whenever new row is inserted (I'll use manual button). I already found & edited some VBA from internet but it doesnt work as intended. If REQUESTER is same, then I want the listdown to be in single email (preferred HTML table like below). As of the VBA i have now,it only takes first row & doesnt listdown second item.
VBA code:
Sub EmailAll()
Dim oApp As Object
Dim oMail As Object
Dim SendToName As String
Dim theSubject As String
Dim theBody As String
For Each c In Selection 'loop through (manually) selected records
'''For each row in selection, collect the key parts of
'''the email message from the Table
On Error Resume Next
SendToName = Range("E" & c.Row)
theSubject = "Goods Arrived for Indent: " & Range("F" & c.Row)
theBody = "This is automated Message" & vbCr & _
"**************************" & vbCr & vbCr & _
"Dear " & Range("D" & c.Row) & vbCr & _
"Your goods been received in store as following:" & vbCr & vbCr & _
"Item: " & Range("H" & c.Row) & vbCr & _
"Qty: " & Range("I" & c.Row)
'''Compose emails for each selected record
'''Set object variables.
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
'''Compose the customized message
With oMail
.To = SendToName
.Subject = theSubject
.Body = theBody
' If you want to generate draft emails and review before sending, use the Display option.
' Do not use both!
'To activate your chosen option: Remove the single quote from the beginning of the code line, then
'add the single quote back to the option you didn't choose
.Display
'.Send
End With
Next c
End Sub
I'm not used to VBA, but is learning slowly. Could someone help to input comment in the VBA also?
PROBLEM:
As below image, I want VBA that will email user whenever new row is inserted (I'll use manual button). I already found & edited some VBA from internet but it doesnt work as intended. If REQUESTER is same, then I want the listdown to be in single email (preferred HTML table like below). As of the VBA i have now,it only takes first row & doesnt listdown second item.
VBA code:
Sub EmailAll()
Dim oApp As Object
Dim oMail As Object
Dim SendToName As String
Dim theSubject As String
Dim theBody As String
For Each c In Selection 'loop through (manually) selected records
'''For each row in selection, collect the key parts of
'''the email message from the Table
On Error Resume Next
SendToName = Range("E" & c.Row)
theSubject = "Goods Arrived for Indent: " & Range("F" & c.Row)
theBody = "This is automated Message" & vbCr & _
"**************************" & vbCr & vbCr & _
"Dear " & Range("D" & c.Row) & vbCr & _
"Your goods been received in store as following:" & vbCr & vbCr & _
"Item: " & Range("H" & c.Row) & vbCr & _
"Qty: " & Range("I" & c.Row)
'''Compose emails for each selected record
'''Set object variables.
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
'''Compose the customized message
With oMail
.To = SendToName
.Subject = theSubject
.Body = theBody
' If you want to generate draft emails and review before sending, use the Display option.
' Do not use both!
'To activate your chosen option: Remove the single quote from the beginning of the code line, then
'add the single quote back to the option you didn't choose
.Display
'.Send
End With
Next c
End Sub