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!!!
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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
I am going to send as an attachment using Outlook
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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
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
Originally Posted by 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
--
Regards Ron de Bruin
http://www.rondebruin.nl
"mdias815" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]>
>> wrote in message
>> news:[email protected]...
>> >
>> > 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
>
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
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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks