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

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!!!


--
mdias815
------------------------------------------------------------------------
mdias815's Profile: http://www.excelforum.com/member.php...o&userid=31509
View this thread: http://www.excelforum.com/showthread...hreadid=511863

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

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 ?

--
Regards Ron de Bruin
http://www.rondebruin.nl


"mdias815" wrote in message
...

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!!!


--
mdias815
------------------------------------------------------------------------
mdias815's Profile: http://www.excelforum.com/member.php...o&userid=31509
View this thread: http://www.excelforum.com/showthread...hreadid=511863

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

I am going to send as an attachment using Outlook


--
mdias815
------------------------------------------------------------------------
mdias815's Profile: http://www.excelforum.com/member.php...o&userid=31509
View this thread: http://www.excelforum.com/showthread...hreadid=511863

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

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




--
Regards Ron de Bruin
http://www.rondebruin.nl


"mdias815" wrote in message
...

I am going to send as an attachment using Outlook


--
mdias815
------------------------------------------------------------------------
mdias815's Profile: http://www.excelforum.com/member.php...o&userid=31509
View this thread: http://www.excelforum.com/showthread...hreadid=511863

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

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 Wrote:
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




--
Regards Ron de Bruin
http://www.rondebruin.nl


"mdias815"
wrote in message
...

I am going to send as an attachment using Outlook


--
mdias815

------------------------------------------------------------------------
mdias815's Profile:
http://www.excelforum.com/member.php...o&userid=31509
View this thread:
http://www.excelforum.com/showthread...hreadid=511863



--
mdias815
------------------------------------------------------------------------
mdias815's Profile: http://www.excelforum.com/member.php...o&userid=31509
View this thread: http://www.excelforum.com/showthread...hreadid=511863

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

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"mdias815" wrote in message
...

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 Wrote:
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




--
Regards Ron de Bruin
http://www.rondebruin.nl


"mdias815"
wrote in message
...

I am going to send as an attachment using Outlook


--
mdias815

------------------------------------------------------------------------
mdias815's Profile:
http://www.excelforum.com/member.php...o&userid=31509
View this thread:
http://www.excelforum.com/showthread...hreadid=511863



--
mdias815
------------------------------------------------------------------------
mdias815's Profile: http://www.excelforum.com/member.php...o&userid=31509
View this thread: http://www.excelforum.com/showthread...hreadid=511863

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

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


--
mdias815
------------------------------------------------------------------------
mdias815's Profile: http://www.excelforum.com/member.php...o&userid=31509
View this thread: http://www.excelforum.com/showthread...hreadid=511863

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

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"mdias815" wrote in message
...

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


--
mdias815
------------------------------------------------------------------------
mdias815's Profile: http://www.excelforum.com/member.php...o&userid=31509
View this thread: http://www.excelforum.com/showthread...hreadid=511863