suppose your email ids are sdtored in sheet 1 and column 1
it will concatenate all the email ids For i = 1 To Sheets(1).Range("a1").End(xlDown).Row all = all & Trim(Sheets(1).Cells(i, 1).Value) & ";" Next i to_mail = Left(all, Len(all) - 1) MsgBox to_mail Set olApp = New Outlook.Application Set olMail = olApp.CreateItem(olMailItem) With olMail .To = to_mail see if it helps On Sat, Feb 5, 2011 at 7:14 PM, BJthebear <brian.jerm...@gmail.com> wrote: > I am trying to amend a VBA macro that I created some time ago using > WinXP and Excel 2003. > > This macro sends a temp worksheet via outlook to the names listed in > the sendMail.array. > > What I am now trying to do is create a dynamic named range somewhere > in the workbook that can be added to or email addresses removed from > it so that this can become the array to which the coding below will > send a copy of the email to. > > > I have no idea where to start - ie creating a dynamic range - > assigning this to an array name and then amending the VBA coding to > reference that array. > > The cutdown coding that I currently have is as follows:- > > > With Destwb > .SaveAs TempFilePath & TempFileName & FileExtStr, _ > FileFormat:=FileFormatNum > On Error Resume Next > For I = 1 To 3 > .SendMail Array("bdj.4...@gmail.com", "bri...@cls.info", > "brian.jerm...@gmail.com"), "Latest Timesheet" > ' "Latest Timesheet" > If Err.Number = 0 Then Exit For > Next I > On Error GoTo 0 > end with > > > > I would be grateful for any advice that will point me in the right > direction??!! > > Thanks in advance > > Brian Jermain > Scotland > > -- > > ---------------------------------------------------------------------------------- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/discussexcel > -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com <http://akoul.blogspot.com/> *akoul*.wordpress.com <http://akoul.wordpress.com/> My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830> P Before printing, think about the environment. -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel