Newbie_Nat
New Member
- Joined
- Jan 8, 2021
- Messages
- 6
- Office Version
- 365
- Platform
- Windows
Hello
I am very new to using VBA scripts! Customer has asked for an automated email using data from a worksheet. This is for holiday requests, so the row will change when a new request is submitted. I have managed to get the following to work to some extent, but not all info is being pulled successfully to the email:
Authorised by is entered in the spreadhseet as NP 06/01/2021 - this is being pulled as 0 in the email. I've tried different types of cell formatting but to no avail.
Data Entered by - this is initials only and is the trigger cell for the email. Nothing is being pulled to the email at all.
I hope I've given enough information for someone to help!?
Many thanks in advance
I am very new to using VBA scripts! Customer has asked for an automated email using data from a worksheet. This is for holiday requests, so the row will change when a new request is submitted. I have managed to get the following to work to some extent, but not all info is being pulled successfully to the email:
VBA Code:
Dim xRg As Range
'Update by Extendoffice 2018/3/7
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
Set xRg = Intersect(Range("M5:M20"), Target)
If xRg Is Nothing Then Exit Sub
If Target.Value > 0 Then
Call Mail_small_Text_Outlook
End If
End Sub
Sub Mail_small_Text_Outlook()
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "Please accept this email as confirmation of your holiday booking - information as below" & vbNewLine & vbNewLine & _
"Date of Request: " & Str(Sheet1.Cells(5, 6)) & vbNewLine & _
"Number of Days Booked:" & Str(Sheet1.Cells(5, 5)) & vbNewLine & vbNewLine & _
"2021 Leave Remaining as at today's date:" & Str(Sheet1.Cells(5, 8)) & " days" & vbNewLine & _
"Data Entered By:" & vbNewLine & vbNewLine & _
"Authorised By:" & Str(Sheet1.Cells(5, 7)) & vbNewLine & vbNewLine & _
"Thank you"
On Error Resume Next
With xOutMail
.To = "****@******.com"
.CC = ""
.BCC = ""
.Subject = "Holiday Booking Confirmation"
.Body = xMailBody
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub
Authorised by is entered in the spreadhseet as NP 06/01/2021 - this is being pulled as 0 in the email. I've tried different types of cell formatting but to no avail.
Data Entered by - this is initials only and is the trigger cell for the email. Nothing is being pulled to the email at all.
I hope I've given enough information for someone to help!?
Many thanks in advance