Hi,
i have an excell sheet that my workshop guy fills out when he is checking a car.
it has a few functions and i am curious if i can combine a couple of bits of code:
This bit of code send an email when a box is ticked to request a Tyre (at the moment it has to be done for each Tyre)
And this bit of code saves and emails the whole sheet as a pdf.
What i would like to happen is when you save the document it looks like whether there is a tick in the box then adds the tyre sizes and counts the amount.
so basically if it needs a Tyre it send two emails one 'as is with the check sheet' and then one with for example '' '2' x '215/55/16' and the reg''
i have an excell sheet that my workshop guy fills out when he is checking a car.
it has a few functions and i am curious if i can combine a couple of bits of code:
This bit of code send an email when a box is ticked to request a Tyre (at the moment it has to be done for each Tyre)
Code:
Sub mail()
Dim objOutlook As Object
Dim objOutlookMsg As Object
Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(0)
With objOutlookMsg
.To = "andrewb@sauto.co.uk"
.Subject = "tyre order sovereign"
.Body = "hi Can i please confirm an order for" & vbNewLine & vbNewLine & "1x " & Worksheets("Sheet1").Range("g10").Value & vbNewLine & vbNewLine & "Reg No:" & Worksheets("Sheet1").Range("H2").Value
.Send
End With
SendMessage = "Your message was sent successfully."
End Sub
And this bit of code saves and emails the whole sheet as a pdf.
Code:
Sub saveandclose()
Dim MyPath As String
Application.DisplayAlerts = False
ActiveWorkbook.saveas Filename:="temp.xlsx"
MyPath = "I:\fleet\Group " & Range("B8").Value & "\" & Range("H2").Value & " - " & Range("B4").Value & " " & Range("E4").Value & "\Check sheet\" & Format(Date, "dd.mm.yy") & ".pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=MyPath
Workbooks.Open Filename:="I:\Check Sheetmacro tin.xltm"
Dim objOutlook As Object
Dim objOutlookMsg As Object
Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(0)
With objOutlookMsg
.To = "andrewb@sauto.co.uk; deans@sauto.co.uk"
.Subject = "New Check Sheet"
'.Attachments.Add ("I:\Users\andrewb.SA\Documents\temp.xlsx")
.Attachments.Add (MyPath)
.Send
End With
SendMessage = "Your message was sent successfully."
Workbooks("temp.xlsx").Close
Application.DisplayAlerts = True
End Sub
What i would like to happen is when you save the document it looks like whether there is a tick in the box then adds the tyre sizes and counts the amount.
so basically if it needs a Tyre it send two emails one 'as is with the check sheet' and then one with for example '' '2' x '215/55/16' and the reg''