Spreadsheet macro groups and prints, now need it to create new documents (1 Viewer)

[esymmonds]

Hi!

I'm hoping someone can help me! The company that I work for produce monthly staff sheets that are sent to Managers to verify thier staff and grades.

We currently do this by importing the data from our payroll system and the running a macro called SVLMerge. This macro then groups together the different paypoints and prints out (directly to printer) the results.

In order to save money, the powers that be would now like us to create a different workbook for each paypoint that can then be emailed out as opposed to snail mail.

I have inherited the spreadsheet that we use to do this and it is a little bit too technical for me.

I have attached what we currently use!!

Thank you in advance for any help!

Emma

 

[noboffinme]

Hi Emma

I have some example code I got from the Internet to send an e-mail below for you to test.

If you could advise how familiar you are with VBA, it would help me to help you.

The next step would be to remove the commands to Print the document & replace them to send an email instead.

The other part is the collating of the results on different spreadsheets - not too hard as you adapt the code you've got.

/code
Sub SendEmail()
'ADD OPTION EXPLICIT
Dim OutlookApp As Object
Dim MItem As Object
Dim cell As Range
Dim Subj As String
Dim EmailAddr As String
Dim Recipient As String
Dim Bonus As String
Dim Msg As String

'Create Outlook object
Set OutlookApp = CreateObject("Outlook.Application")

'Loop through the rows
For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "*@*" Then
'Get the data
Subj = "Your Annual Bonus"
Recipient = cell.Offset(0, -1).Value
EmailAddr = cell.Value
Bonus = Format(cell.Offset(0, 1).Value, "$0,000.")

'Compose message
Msg = "Dear " & Recipient & vbCrLf & vbCrLf
Msg = Msg & "I am pleased to inform you that "
Msg = Msg & "your annual bonus is "
Msg = Msg & Bonus & vbCrLf & vbCrLf
Msg = Msg & "William Rose" & vbCrLf
Msg = Msg & "President"

'Create Mail Item and send it
Set MItem = OutlookApp.CreateItem(0)
With MItem
.To = EmailAddr
.Subject = Subj
.Body = Msg
.Display
'NOTE: To actually send the emails, use .Send instead of .Display
'.Send
End With
End If
Next
End Sub
code/

 

[esymmonds]

Hi Thanks for this!!

I'll have a look over it and see what I can do with it!! I'm not bad with VBA, it's something I duck in and out of when I need it!

Emma

 

[noboffinme]

Great!

Let me know if you need any more help.