Hi, I don't normally use VBA so I apologise for my lack of knowledge!
I have a spreadsheet that has a header row, then each person has their own row with their own data below it. I need to run a macro to send these people an email with only their line of data.
So far I have managed to create the macro very simply, a sample of the code is below so that it takes the email address from Column J and creates an email and moves on to the next row.
The issue I have is that when it puts the "Time" in (I have 3 times to add to the email) it converts it from 01:40:38 to a number like 4.023453-2.
I have looked at every page on the internet to find an easy work around and can't come up with one! Does anyone know how I can format the time to stay as hh:mm:ss as it is the most important thing!
Any help is really appreciated as I am losing what little hair I have left!
Sub test2()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
For Each cell In Worksheets("Sheet1").Columns("J").Cells
Set OutMail = OutApp.CreateItem(0)
If cell.Value Like "?*@?*.?*" Then 'try with less conditions first
With OutMail
.To = Cells(cell.Row, "J").Value
.Subject = "Test Email"
.Body = "Date: " & Cells(cell.Row, "A").Value & vbNewLine & _
"Name: " & Cells(cell.Row, "B").Value & vbNewLine & _
"Time: " & Cells(cell.Row, "C").Value & vbNewLine & _
"Time2: " & Cells(cell.Row, "D").Value & vbNewLine & _
"Time3: " & Cells(cell.Row, "E").Value & vbNewLine & _
.display
End With
Cells(cell.Row, "M").Value = "sent"
Set OutMail = Nothing
End If
Next cell
Application.ScreenUpdating = True
End Sub
I have a spreadsheet that has a header row, then each person has their own row with their own data below it. I need to run a macro to send these people an email with only their line of data.
So far I have managed to create the macro very simply, a sample of the code is below so that it takes the email address from Column J and creates an email and moves on to the next row.
The issue I have is that when it puts the "Time" in (I have 3 times to add to the email) it converts it from 01:40:38 to a number like 4.023453-2.
I have looked at every page on the internet to find an easy work around and can't come up with one! Does anyone know how I can format the time to stay as hh:mm:ss as it is the most important thing!
Any help is really appreciated as I am losing what little hair I have left!
Sub test2()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
For Each cell In Worksheets("Sheet1").Columns("J").Cells
Set OutMail = OutApp.CreateItem(0)
If cell.Value Like "?*@?*.?*" Then 'try with less conditions first
With OutMail
.To = Cells(cell.Row, "J").Value
.Subject = "Test Email"
.Body = "Date: " & Cells(cell.Row, "A").Value & vbNewLine & _
"Name: " & Cells(cell.Row, "B").Value & vbNewLine & _
"Time: " & Cells(cell.Row, "C").Value & vbNewLine & _
"Time2: " & Cells(cell.Row, "D").Value & vbNewLine & _
"Time3: " & Cells(cell.Row, "E").Value & vbNewLine & _
.display
End With
Cells(cell.Row, "M").Value = "sent"
Set OutMail = Nothing
End If
Next cell
Application.ScreenUpdating = True
End Sub
