need help to create multiple copies of sheet for different sales reps

[mdias815]

Hopefully someone can help me save a ton of time. I have a list lof
sales data that I have created a template using a ms query. For each
sales rep I want to send them only their information. On my template I
just type in their sales code and their accounts come up. Is there a
way to automate this process without having to do each of these
manually we have 60 different reps. HELP!!!

 

[Ron de Bruin]

Hi mdias815

Do you want to send them the info with mail ?
Attachment or in the body of the mail ?
Which mail program do you use ?

 

[mdias815]

I am going to send as an attachment using Outlook

 

[Ron de Bruin]

Here is a tester that display the mails with the attachment

Change this

Set DataSheet = Sheets("Sheet1")
Set InfoSheet = Sheets("SalesCode")

Sheet1 is the template with cell D1 as your input cell

'D1 is the Sales code cell
DataSheet.Range("D1").Value = cell.Value


In Sheets("SalesCode") in A1:A60 the sales codes and in column B the E-Mail addresses



Sub test()
Dim OutApp As Object
Dim OutMail As Object
Dim wb As Workbook
Dim DataSheet As Worksheet
Dim InfoSheet As Worksheet
Dim cell As Range
Dim strdate As String

strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False

Set DataSheet = Sheets("Sheet1")
Set InfoSheet = Sheets("SalesCode")

For Each cell In InfoSheet.Range("A1:A60")
If cell.Offset(0, 1).Value Like "?*@?*.?*" Then

'D1 is the Sales code cell
DataSheet.Range("D1").Value = cell.Value
DataSheet.Copy

Set wb = ActiveWorkbook
With wb
.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = cell.Offset(0, 1).Value
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add wb.FullName
.Display 'or use .Send
End With
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With

Set OutMail = Nothing
Set OutApp = Nothing
End If
Next cell
Application.ScreenUpdating = True

End Sub

 

[mdias815]

Thanks so Much. Is there any way to change the name of the worksheet to
match the sales code for each one. Also is there a way to remove the
query so that each rep does not have access to other information

 

[Ron de Bruin]

Hi

..SaveAs Cell.Value _
& " " & strdate & ".xls"

Also is there a way to remove the
query so that each rep does not have access to other information

Can you tell me more what you want
On this moment it only send one worksheet

 

[mdias815]

I have a query to external data in my worksheet. When each new sheet is
created i need the query to update to the new sales code and then
remove the query definition

 

[Ron de Bruin]

I don't think I understand you

You say

just type in their sales code and their accounts come up

So if it is correct the mails are created with one sheet with the correct data for each sales person
Am I correct ?

If you have a query in the workbook it is not in the sheet you send