VBA Code to Send Pay Slip automatic from Folder

[kumar lama]

I have Pay slip in one folder "C:\Users\ABC\Desktop\All My Desktop File\SS\Salary File\Staff\6. Oct-14\PaySlip"
I have one Excel File sheet 1 which has all employees Name (Column "A: A") All Name are match with folder pays lip name and Column "B: B" has email address.

So is there any way to send pay slip automatic by outlook? I think there are some VBA Code to do it. And all my Payslip are in PDF. I am using VBA code to save as PDF.
Code are like below.
Option Explicit

Public Sub SaveAsPdf()
Dim lStart As Long
Dim lEnd As Long
Dim l As Long
Dim rngSlipNumber As Range


With Sheet5
Set rngSlipNumber = .Range("AJ1")
lStart = .Range("Ak1") ' indicates starting Sr num
lEnd = .Range("Ak2") ' indicates ending Sr num


For l = lStart To lEnd
rngSlipNumber.Value = l
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=.Range(" AI1") & "-" & "Mr. " & .Range("D8") & " " & .Range(" AR1").Value, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Next l
End With
End Sub

 

[mrmmickle1]

Here is some basic code that I adapted from Ron De Bruin to send an entire workbook via outlook email :

[COLOR=#0000ff]Sub[/COLOR] Mail_workbook_Outlook_1()


[COLOR=#0000ff]    Dim [/COLOR]OutApp[COLOR=#0000ff] As Object[/COLOR]
   [COLOR=#0000ff] Dim[/COLOR] OutMail [COLOR=#0000ff]As Object[/COLOR]


 [COLOR=#0000ff]   Set[/COLOR] OutApp = CreateObject("Outlook.Application")
  [COLOR=#0000ff]  Set[/COLOR] OutMail = OutApp.CreateItem(0)


[COLOR=#0000ff]    On Error Resume Next[/COLOR]
  [COLOR=#0000ff]  With[/COLOR] OutMail
        .to = "someone@email.com"
        .CC = "somone.else@email.com"
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = "Hi there"
        .Attachments.Add ActiveWorkbook.FullName
[COLOR=#008000]        'You can add other files also like this[/COLOR]
[COLOR=#008000]        '.Attachments.Add ("C:\test.txt")[/COLOR]
        .Send  [COLOR=#008000] 'or use .Display[/COLOR]
[COLOR=#0000ff]    End With[/COLOR]
[COLOR=#0000ff]    On Error GoTo 0[/COLOR][COLOR=#008000] 'Reset Error Handling[/COLOR][COLOR=#0000ff]

[/COLOR]
[COLOR=#008000]     'Clear Variables[/COLOR]
    [COLOR=#0000ff]Set [/COLOR]OutMail = [COLOR=#0000ff]Nothing[/COLOR]
[COLOR=#0000ff]    Set[/COLOR] OutApp =[COLOR=#0000ff] Nothing[/COLOR]


[COLOR=#0000ff]End Sub[/COLOR]

 

[kumar lama]

Hi Mrmmickle1
Thank you for your Code but as this code i have to use one by one email adress, every time i have to change code. but what i want is sent all email in one time. i have all email adress in one excel sheet column B and Employee Name Column A.

Sub Mail_workbook_Outlook_1()

Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.to = "kumarlama.lama1@gmail.com" ' Insted of individual Emal i want Range of emali adress base on Name
.CC = ""
.BCC = ""
.Subject = "Payslip"
.Body = "Hi there"
.Attachments.Add ActiveWorkbook.FullName
'You can add other files also like this
Attachments.Add ("C:\Users\chand\Desktop\All\Samsung File\Salary File\Staff\6. Oct-14\PaySlip\Not Signed\39-Mr. ABC Payslip (Oct-2014).pdf")
.Send 'or use .Display
End With
On Error GoTo 0 'Reset Error Handling

'Clear Variables
Set OutMail = Nothing
Set OutApp = Nothing

End Sub


Thank you

 

[Teeroy]

I think you misunderstood @mrmmickle1. He gave you a method to adapt, not a solution. You need to wrap the mail creation in a loop to send multiple items, e.g. (untested)

Sub Mail_workbook_Outlook_2()
Dim OutApp As Object, OutMail As Object
Dim lr As Long
Dim Rng As Range
Dim sBaseFileName as string, sFilename as string

Set OutApp = CreateObject("Outlook.Application")
lr = Cells(Rows.Count, 1).End(xlUp).Row
sBaseFileName = "C:\Users\ABC\Desktop\All My Desktop File\SS\Salary File\Staff\6. Oct-14\PaySlip"

For Each Rng In Range("A2:A" & lr)
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail
        .to = Rng.Offset(0, 1).Value ' Insted of individual Emal i want Range of emali adress base on Name
        .CC = ""
        .BCC = ""
        .Subject = "Payslip"
        .Body = "Hi there"
        '.Attachments.Add ActiveWorkbook.FullName 'Not necessary for your needs
        'You can add other files also like this
        sFilename = sBaseFileName & "\" & Rng.Value & ".pdf"
        .Attachments.Add (sFilename)
        .Send 'or use .Display
    End With
    Set OutMail = Nothing
    On Error GoTo 0 'Reset Error Handling
Next
'Clear Variables
Set OutApp = Nothing
End Sub

This may give you a security warning in outlook but you can change this using the method given at Disable the mail security dialog to Send/not Send the mail

 

[kumar lama]

Wow Great Help!! Thank you so much Teeroy

 

[mrmmickle1]

Glad to help out! Let me know if you need anything further. You can put something like the above in a loop to send out multiple emails if need be...

 

[kumar lama]

Hello I Have one Question On Body i want put massage like Below how can i do?
.Body = "Hi there"
Next Paragraph
PLease find Your Payslip
Next Paragraph
Thank you
How can i do it?

 

[mrmmickle1]

Try something like this:

vblf ---> LineFeed

[COLOR=#0000ff]Dim[/COLOR] BodyStr [COLOR=#0000ff]As String[/COLOR]


BodyStr = "Hi there,"
BodyStr = BodyStr & vbLf & "Please find Your Payslip"
BodyStr = BodyStr & vbLf & "Thank you"
 
.Body = BodyStr

 

[VkasP]

Try something like this:

vblf ---> LineFeed

[COLOR=#0000ff]Dim[/COLOR] BodyStr [COLOR=#0000ff]As String[/COLOR]


BodyStr = "Hi there,"
BodyStr = BodyStr & vbLf & "Please find Your Payslip"
BodyStr = BodyStr & vbLf & "Thank you"
 
.Body = BodyStr

Hi Expert,

Grateful to have a template of VBA code to send payslip automatic from folder.

many many thanks

 

[SHOUKAT SHAIKH]

i have excel data of salary sheet, I want to send pdf of each data as payslip through outlook in pdf format.