|
Email files on C Drive
Hi All,
Have found similar postings, but not enough to go on in regards to the following. I currently have some VBA that does the following: - Creates individual file (with all the relevant records) for each unique name contained within Column G - Saves the file (with the name of the unique entry) .xls What I'd like to do is extend this to be able to automatically send an e-mail with pre-set text to advise the users (user is the unique entry in column G) what they next need to do (so I'd need to include some e- mail text as well). The unique entry isn't the SMTP address, it's the internal user's name as it appears in our address book (e.g. Smith, Bob) I'd like it instead of automatically sending, at least at first, to prompt the person who's running it as to whether they want to send at first. So, in summary: 1) Run through all xls files on C Drive and attach the file to an e- mail with some pre-defined subject/content text 2) Populate the to box on the e-mail with the file name (less the .xls part) Appreciate your help on this. Many Thanks, Al. |
Email files on C Drive
Check out this link to get started...
http://www.rondebruin.nl/sendmail.htm -- HTH... Jim Thomlinson " wrote: Hi All, Have found similar postings, but not enough to go on in regards to the following. I currently have some VBA that does the following: - Creates individual file (with all the relevant records) for each unique name contained within Column G - Saves the file (with the name of the unique entry) .xls What I'd like to do is extend this to be able to automatically send an e-mail with pre-set text to advise the users (user is the unique entry in column G) what they next need to do (so I'd need to include some e- mail text as well). The unique entry isn't the SMTP address, it's the internal user's name as it appears in our address book (e.g. Smith, Bob) I'd like it instead of automatically sending, at least at first, to prompt the person who's running it as to whether they want to send at first. So, in summary: 1) Run through all xls files on C Drive and attach the file to an e- mail with some pre-defined subject/content text 2) Populate the to box on the e-mail with the file name (less the .xls part) Appreciate your help on this. Many Thanks, Al. |
Email files on C Drive
Thanks Jim.
This site has been really useful. I can now e-mail the range of data (as a HTML formatted text), but can't seem to send it as an Excel attachment instead? Can you help with pointing me in the direction to send the range as an excel attachment? Thanks, Al. So far: Option Explicit Dim ws1 As Worksheet Dim ws2 As Worksheet Dim rng As Range Dim cell As Range Dim Lrow As Long Public Sub Test_With_AdvancedFilter() Application.ScreenUpdating = False Set ws1 = Sheets("Data") Set ws2 = Worksheets.Add Set rng = ws1.Range("A1:I395") With ws1 rng.Columns(7).AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=.Range("IV1"), Unique:=True Lrow = .Cells(Rows.Count, "IV").End(xlUp).Row .Range("IU1").Value = .Range("IV1").Value For Each cell In .Range("IV2:IV" & Lrow) .Range("IU2").Value = cell.Value ws2.Cells.ClearContents rng.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=.Range("IU1:IU2"), _ CopyToRange:=ws2.Range("A1"), _ Unique:=False .Columns.AutoFit FormatUserWB Mail_ActiveSheet_Body Next .Columns("IU:IV").Clear End With Application.DisplayAlerts = False ws2.Delete Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub Private Sub Mail_ActiveSheet_Body() Dim OutApp As Object Dim OutMail As Object Application.ScreenUpdating = False Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) With OutMail .To = ws2.Range("G2").Value .CC = "" .BCC = "" .Subject = "This is the Subject line" .HTMLBody = SheetToHTML(ws2) .display 'or use .Send End With Application.ScreenUpdating = True Set OutMail = Nothing Set OutApp = Nothing End Sub Public Function SheetToHTML(sh As Worksheet) Dim TempFile As String Dim fso As Object Dim ts As Object Randomize sh.Copy TempFile = sh.Parent.Path & "\TmpHTML" & Int(Rnd() * 10) & ".htm" ActiveWorkbook.SaveAs TempFile, xlHtml ActiveWorkbook.Close False Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) SheetToHTML = ts.ReadAll ts.Close Set ts = Nothing Set fso = Nothing Kill TempFile End Function On Apr 14, 4:42*pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move- This-.com wrote: Check out this link to get started... http://www.rondebruin.nl/sendmail.htm -- HTH... Jim Thomlinson " wrote: Hi All, Have found similar postings, but not enough to go on in regards to the following. I currently have some VBA that does the following: *- Creates individual file (with all the relevant records) for each unique name contained within Column G *- Saves the file (with the name of the unique entry) .xls What I'd like to do is extend this to be able to automatically send an e-mail with pre-set text to advise the users (user is the unique entry in column G) what they next need to do (so I'd need to include some e- mail text as well). *The unique entry isn't the SMTP address, it's the internal user's name as it appears in our address book (e.g. Smith, Bob) I'd like it instead of automatically sending, at least at first, to prompt the person who's running it as to whether they want to send at first. So, in summary: 1) *Run through all xls files on C Drive and attach the file to an e- mail with some pre-defined subject/content text 2) *Populate the to box on the e-mail with the file name (less the .xls part) Appreciate your help on this. Many Thanks, Al.- Hide quoted text - - Show quoted text - |
Email files on C Drive
See
http://www.rondebruin.nl/mail/folder2/mail4.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm wrote in message ... Thanks Jim. This site has been really useful. I can now e-mail the range of data (as a HTML formatted text), but can't seem to send it as an Excel attachment instead? Can you help with pointing me in the direction to send the range as an excel attachment? Thanks, Al. So far: Option Explicit Dim ws1 As Worksheet Dim ws2 As Worksheet Dim rng As Range Dim cell As Range Dim Lrow As Long Public Sub Test_With_AdvancedFilter() Application.ScreenUpdating = False Set ws1 = Sheets("Data") Set ws2 = Worksheets.Add Set rng = ws1.Range("A1:I395") With ws1 rng.Columns(7).AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=.Range("IV1"), Unique:=True Lrow = .Cells(Rows.Count, "IV").End(xlUp).Row .Range("IU1").Value = .Range("IV1").Value For Each cell In .Range("IV2:IV" & Lrow) .Range("IU2").Value = cell.Value ws2.Cells.ClearContents rng.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=.Range("IU1:IU2"), _ CopyToRange:=ws2.Range("A1"), _ Unique:=False .Columns.AutoFit FormatUserWB Mail_ActiveSheet_Body Next .Columns("IU:IV").Clear End With Application.DisplayAlerts = False ws2.Delete Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub Private Sub Mail_ActiveSheet_Body() Dim OutApp As Object Dim OutMail As Object Application.ScreenUpdating = False Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) With OutMail .To = ws2.Range("G2").Value .CC = "" .BCC = "" .Subject = "This is the Subject line" .HTMLBody = SheetToHTML(ws2) .display 'or use .Send End With Application.ScreenUpdating = True Set OutMail = Nothing Set OutApp = Nothing End Sub Public Function SheetToHTML(sh As Worksheet) Dim TempFile As String Dim fso As Object Dim ts As Object Randomize sh.Copy TempFile = sh.Parent.Path & "\TmpHTML" & Int(Rnd() * 10) & ".htm" ActiveWorkbook.SaveAs TempFile, xlHtml ActiveWorkbook.Close False Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) SheetToHTML = ts.ReadAll ts.Close Set ts = Nothing Set fso = Nothing Kill TempFile End Function On Apr 14, 4:42 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move- This-.com wrote: Check out this link to get started... http://www.rondebruin.nl/sendmail.htm -- HTH... Jim Thomlinson " wrote: Hi All, Have found similar postings, but not enough to go on in regards to the following. I currently have some VBA that does the following: - Creates individual file (with all the relevant records) for each unique name contained within Column G - Saves the file (with the name of the unique entry) .xls What I'd like to do is extend this to be able to automatically send an e-mail with pre-set text to advise the users (user is the unique entry in column G) what they next need to do (so I'd need to include some e- mail text as well). The unique entry isn't the SMTP address, it's the internal user's name as it appears in our address book (e.g. Smith, Bob) I'd like it instead of automatically sending, at least at first, to prompt the person who's running it as to whether they want to send at first. So, in summary: 1) Run through all xls files on C Drive and attach the file to an e- mail with some pre-defined subject/content text 2) Populate the to box on the e-mail with the file name (less the .xls part) Appreciate your help on this. Many Thanks, Al.- Hide quoted text - - Show quoted text - |
Email files on C Drive
Thanks all for your help on this. The website is superb, really
useful source of info'. I have just one question/requirement that I couldn't find answer for now. Is it possible to automatically populate the 'From' box @ all? Thanks in advance, Al. On Apr 14, 5:32*pm, "Ron de Bruin" wrote: Seehttp://www.rondebruin.nl/mail/folder2/mail4.htm -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm wrote in ... Thanks Jim. This site has been really useful. I can now e-mail the range of data (as a HTML formatted text), but can't seem tosendit as an Excel attachment instead? Can you help with pointing me in the direction tosendthe range as an excel attachment? Thanks, Al. So far: Option Explicit Dim ws1 As Worksheet Dim ws2 As Worksheet Dim rng As Range Dim cell As Range Dim Lrow As Long Public Sub Test_With_AdvancedFilter() * * Application.ScreenUpdating = False * * Set ws1 = Sheets("Data") * * Set ws2 = Worksheets.Add * * Set rng = ws1.Range("A1:I395") * * With ws1 * * * * rng.Columns(7).AdvancedFilter _ * * * * * * * * Action:=xlFilterCopy, _ * * * * * * * * CopyToRange:=.Range("IV1"), Unique:=True * * * * Lrow = .Cells(Rows.Count, "IV").End(xlUp).Row * * * * .Range("IU1").Value = .Range("IV1").Value * * * * For Each cell In .Range("IV2:IV" & Lrow) * * * * * * .Range("IU2").Value = cell.Value * * * * * * ws2.Cells.ClearContents * * * * * * rng.AdvancedFilter Action:=xlFilterCopy, _ * * * * * * * * * * * * * * * *CriteriaRange:=.Range("IU1:IU2"), _ * * * * * * * * * * * * * * * *CopyToRange:=ws2.Range("A1"), _ * * * * * * * * * * * * * * * *Unique:=False * * * * * * .Columns.AutoFit * * * * * * FormatUserWB * * * * * * Mail_ActiveSheet_Body * * * * Next * * * * .Columns("IU:IV").Clear * * End With * * Application.DisplayAlerts = False * * ws2.Delete * * Application.DisplayAlerts = True * * Application.ScreenUpdating = True End Sub Private Sub Mail_ActiveSheet_Body() * * Dim OutApp As Object * * Dim OutMail As Object * * Application.ScreenUpdating = False * * Set OutApp = CreateObject("Outlook.Application") * * Set OutMail = OutApp.CreateItem(0) * * With OutMail * * * * .To = ws2.Range("G2").Value * * * * .CC = "" * * * * .BCC = "" * * * * .Subject = "This is the Subject line" * * * * .HTMLBody = SheetToHTML(ws2) * * * * .display * 'or use .Send * * End With * * Application.ScreenUpdating = True * * Set OutMail = Nothing * * Set OutApp = Nothing End Sub Public Function SheetToHTML(sh As Worksheet) * * Dim TempFile As String * * Dim fso As Object * * Dim ts As Object * * Randomize * * sh.Copy * * TempFile = sh.Parent.Path & "\TmpHTML" & Int(Rnd() * 10) & ".htm" * * ActiveWorkbook.SaveAs TempFile, xlHtml * * ActiveWorkbook.Close False * * Set fso = CreateObject("Scripting.FileSystemObject") * * Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) * * SheetToHTML = ts.ReadAll * * ts.Close * * Set ts = Nothing * * Set fso = Nothing * * Kill TempFile End Function On Apr 14, 4:42 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move- This-.com wrote: Check out this link to get started... http://www.rondebruin.nl/sendmail.htm -- HTH... Jim Thomlinson " wrote: Hi All, Have found similar postings, but not enough to go on in regards to the following. I currently have some VBA that does the following: - Creates individual file (with all the relevant records) for each unique name contained within Column G - Saves the file (with the name of the unique entry) .xls What I'd like to do is extend this to be able to automaticallysendan e-mail with pre-set text to advise the users (user is the unique entry in column G) what they next need to do (so I'd need to include some e- mail text as well). The unique entry isn't the SMTP address, it's the internal user's name as it appears in our address book (e.g. Smith, Bob) I'd like it instead of automatically sending, at least at first, to prompt the person who's running it as to whether they want tosendat first. So, in summary: 1) Run through all xls files on C Drive and attach the file to an e- mail with some pre-defined subject/content text 2)Populatethe to box on the e-mail with the file name (less the .xls part) Appreciate your help on this. Many Thanks, Al.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Email files on C Drive
Apologies for the further update, I then found the tips page and found
the answer to this. Once again, fantastic web-site. Cheers, Al. On Apr 15, 12:16*pm, wrote: Thanks all for your help on this. *The website is superb, really useful source of info'. I have just one question/requirement that I couldn't find answer for now. *Is it possible to automatically populate the 'From' box @ all? Thanks in advance, Al. On Apr 14, 5:32*pm, "Ron de Bruin" wrote: Seehttp://www.rondebruin.nl/mail/folder2/mail4.htm -- Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm wrote in ... Thanks Jim. This site has been really useful. I can now e-mail the range of data (as a HTML formatted text), but can't seem tosendit as an Excel attachment instead? Can you help with pointing me in the direction tosendthe range as an excel attachment? Thanks, Al. So far: Option Explicit Dim ws1 As Worksheet Dim ws2 As Worksheet Dim rng As Range Dim cell As Range Dim Lrow As Long Public Sub Test_With_AdvancedFilter() * * Application.ScreenUpdating = False * * Set ws1 = Sheets("Data") * * Set ws2 = Worksheets.Add * * Set rng = ws1.Range("A1:I395") * * With ws1 * * * * rng.Columns(7).AdvancedFilter _ * * * * * * * * Action:=xlFilterCopy, _ * * * * * * * * CopyToRange:=.Range("IV1"), Unique:=True * * * * Lrow = .Cells(Rows.Count, "IV").End(xlUp).Row * * * * .Range("IU1").Value = .Range("IV1").Value * * * * For Each cell In .Range("IV2:IV" & Lrow) * * * * * * .Range("IU2").Value = cell.Value * * * * * * ws2.Cells.ClearContents * * * * * * rng.AdvancedFilter Action:=xlFilterCopy, _ * * * * * * * * * * * * * * * *CriteriaRange:=.Range("IU1:IU2"), _ * * * * * * * * * * * * * * * *CopyToRange:=ws2.Range("A1"), _ * * * * * * * * * * * * * * * *Unique:=False * * * * * * .Columns.AutoFit * * * * * * FormatUserWB * * * * * * Mail_ActiveSheet_Body * * * * Next * * * * .Columns("IU:IV").Clear * * End With * * Application.DisplayAlerts = False * * ws2.Delete * * Application.DisplayAlerts = True * * Application.ScreenUpdating = True End Sub Private Sub Mail_ActiveSheet_Body() * * Dim OutApp As Object * * Dim OutMail As Object * * Application.ScreenUpdating = False * * Set OutApp = CreateObject("Outlook.Application") * * Set OutMail = OutApp.CreateItem(0) * * With OutMail * * * * .To = ws2.Range("G2").Value * * * * .CC = "" * * * * .BCC = "" * * * * .Subject = "This is the Subject line" * * * * .HTMLBody = SheetToHTML(ws2) * * * * .display * 'or use .Send * * End With * * Application.ScreenUpdating = True * * Set OutMail = Nothing * * Set OutApp = Nothing End Sub Public Function SheetToHTML(sh As Worksheet) * * Dim TempFile As String * * Dim fso As Object * * Dim ts As Object * * Randomize * * sh.Copy * * TempFile = sh.Parent.Path & "\TmpHTML" & Int(Rnd() * 10) & ".htm" * * ActiveWorkbook.SaveAs TempFile, xlHtml * * ActiveWorkbook.Close False * * Set fso = CreateObject("Scripting.FileSystemObject") * * Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) * * SheetToHTML = ts.ReadAll * * ts.Close * * Set ts = Nothing * * Set fso = Nothing * * Kill TempFile End Function On Apr 14, 4:42 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move- This-.com wrote: Check out this link to get started... http://www.rondebruin.nl/sendmail.htm -- HTH... Jim Thomlinson " wrote: Hi All, Have found similar postings, but not enough to go on in regards to the following. I currently have some VBA that does the following: - Creates individual file (with all the relevant records) for each unique name contained within Column G - Saves the file (with the name of the unique entry) .xls What I'd like to do is extend this to be able to automaticallysendan e-mail with pre-set text to advise the users (user is the unique entry in column G) what they next need to do (so I'd need to include some e- mail text as well). The unique entry isn't the SMTP address, it's the internal user's name as it appears in our address book (e.g. Smith, Bob) I'd like it instead of automatically sending, at least at first, to prompt the person who's running it as to whether they want tosendat first. So, in summary: 1) Run through all xls files on C Drive and attach the file to an e- mail with some pre-defined subject/content text 2)Populatethe to box on the e-mail with the file name (less the .xls part) Appreciate your help on this. Many Thanks, Al.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| All times are GMT +1. The time now is 04:59 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com