hiya folks.
I have the following Macro running to send email to suppliers if orders are due or late
Sub CommandButton1_Click()
Dim OutApp As Object, OutMail As Object
Dim rng As Range
Dim StrBody As String
StrBody = "Please see below Live PO's requiring confirmation & updates. for any lines which will not arrive on the stated date, please amend and return accordingly, please also confirm the pricing is inline with your system to avoid payment delays" & "" & _
""
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
Set rng = Nothing
On Error Resume Next
Set rng = Sheets("sheet2").Range("h4")
On Error GoTo cleanup
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.Importance = 2
.To = [H1]
.cc = [Bg1]
.bcc = [bi1]
.Subject = "Peacocks Require PO Update - test only"
.HTMLBody = StrBody & RangetoHTML(rng)
.Display 'Or use .Send
End With
On Error GoTo 0
Set OutMail = Nothing
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub
I would like to loop through cells a4 to a80 and if in range c4 to c80 <>"", enter a "*" in the relevant row in column A, run the above code and move to next line until end of the C range?
The qty of rows can change within C so the loop needs to consider this?
Is this possible? Many Thasnks
I have the following Macro running to send email to suppliers if orders are due or late
Sub CommandButton1_Click()
Dim OutApp As Object, OutMail As Object
Dim rng As Range
Dim StrBody As String
StrBody = "Please see below Live PO's requiring confirmation & updates. for any lines which will not arrive on the stated date, please amend and return accordingly, please also confirm the pricing is inline with your system to avoid payment delays" & "" & _
""
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
Set rng = Nothing
On Error Resume Next
Set rng = Sheets("sheet2").Range("h4")
On Error GoTo cleanup
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.Importance = 2
.To = [H1]
.cc = [Bg1]
.bcc = [bi1]
.Subject = "Peacocks Require PO Update - test only"
.HTMLBody = StrBody & RangetoHTML(rng)
.Display 'Or use .Send
End With
On Error GoTo 0
Set OutMail = Nothing
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub
I would like to loop through cells a4 to a80 and if in range c4 to c80 <>"", enter a "*" in the relevant row in column A, run the above code and move to next line until end of the C range?
The qty of rows can change within C so the loop needs to consider this?
Is this possible? Many Thasnks
