PDA

View Full Version : Code for formatting Email from Excel



DontKnow
12-11-2010, 12:14 AM
Hi All

I am relative new to coding - learnt about a week ago - so I dont really know too much. I am generating a Macro which will take data from Excel and send it in an email.

Code is:
Sub PayslipEmail()
Dim Email As String, Subj As String
Dim Msg As String, URL As String
Dim r As Integer, x As Double
For r = 3 To 3
Email = Cells(r, 1)
Subj = "Allstaff Australia Payslip " & Cells(r, 348)
Msg = ""
Msg = Msg & Cells(r, 269).Text & " - Payment Advice" & "." & vbCrLf & vbCrLf
Msg = Msg & "Employer: " & Cells(r, 84).Text & " - " & Cells(r, 250).Text & "." & " " & "ABN: " & Cells(r, 2).Text & "." & vbCrLf & vbCrLf
Msg = Msg & "Employee Code: " & Cells(r, 106).Text & "." & vbCrLf
Msg = Msg & "Pay Date: " & Cells(r, 152).Text & "." & " " & "Week Ending: " & Cells(r, 348) & vbCrLf
Msg = Msg & "YTD Gross Paid: " & "$" & Cells(r, 356).Text & ".00" & "." & " " & "YTD PAYG Tax: " & "$" & Cells(r, 357).Text & ".00" & "." & vbCrLf & vbCrLf
Msg = Msg & "Name: " & Cells(r, 118).Text & "." & vbCrLf
Msg = Msg & "Address: " & Cells(r, 3) & " " & Cells(r, 4) & " " & Cells(r, 278).Text & "." & vbCrLf & vbCrLf
Msg = Msg & "Client: " & Cells(r, 79).Text & "." & " - " & "Employment Type: " & Cells(r, 109).Text & "." & vbCrLf
Msg = Msg & "Award: " & Cells(r, 68).Text & "." & " - " & "Grade / Level: " & Cells(r, 119).Text & "." & vbCrLf & vbCrLf
Msg = Msg & "HOURS PAID" & vbCrLf
Msg = Msg & "Shift: " & Cells(r, 168).Text & ", " & "Hours: " & Cells(r, 171).Text & ", " & "Rate: $" & Cells(r, 169) & ", " & "Sum: $" & Cells(r, 170) & vbCrLf
Msg = Msg & "Shift: " & Cells(r, 174).Text & ", " & "Hours: " & Cells(r, 177).Text & ", " & "Rate: $" & Cells(r, 175) & ", " & "Sum: $" & Cells(r, 176) & vbCrLf
Msg = Msg & "Shift: " & Cells(r, 180).Text & ", " & "Hours: " & Cells(r, 183).Text & ", " & "Rate: $" & Cells(r, 181) & ", " & "Sum: $" & Cells(r, 182) & vbCrLf
Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")
Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")
URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg
ActiveWorkbook.FollowHyperlink (URL)
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%s"
Next r
End Sub

When the text goes into the body of the email I want to be able tab along the format so shift, hours, rate and sum values line up in each row of the email.

Thanks for any help



.