MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Using combined cell values to refer to Workbooks


Posted by John Farren on March 30, 2001 7:34 AM

Trying to make and mail weekly a batch of Workbooks from a single Workbook, using group name and current date from original as the new filenames.
Creating bit works, but then I want to use the same text combining to identify the workbooks to email (and close and delete).
Can't do it. Was trying what I thought vaguely might work:

strWorkbookName = strFacGroup & "_" & strDate & ".xls"
Workbooks(strWorkbookName).Activate
Application.Dialogs(xlDialogSendMail).Show arg1:=strEmailAddress, arg2:="This weeks dispatch information"

Produces a "subscript out of range" error message.
Any ideas as to if/how I can do this?
Any help would be v. greatly appreciated.


Posted by Dave Hawley on March 31, 2001 3:53 AM


Hi John

You would need to actually window through to the Workbook first, so try changing your code to this:



strWorkbookName = strFacGroup & "_" & strDate & ".xls"
Windows(strWorkbookName).Activate
Application.Dialogs(xlDialogSendMail).Show arg1:=strEmailAddress, arg2:="This weeks dispatch information"

Dave
OzGrid Business Applications

Posted by John Farren on April 02, 2001 3:49 AM

Thanks very much indeed, Dave.