I do something wrong with SendMail macro...Please help

Hello! I would like to refer to Ron de Bruin's macro that should make i
possible to mail sheet or sheets to one or more people, described o
his I-net site; www.rondebruin.nl/sendmail.htm

I added a sheet "mail" to my workbook and added 6 columns(needed t
send out the two sheets that I would like to send) :
The macro looks like this:

Sub Mail_sheets()
Dim MyArr As Variant
Dim a As Integer
Dim Arr() As String
Dim N As Integer
Dim strdate As String
Dim wb As Workbook
Dim cell As Range

For a = 1 To 253 Step 3
If ThisWorkbook.Sheets("mail").Cells(2, a).Value = "" Then Exi
Sub
Application.ScreenUpdating = False
strdate = Format(Now, "dd-mm-yy h-mm-ss")

With ThisWorkbook.Sheets("mail")
MyArr = .Range(.Cells(2, a + 1), .Cells(Rows.Count, a 
1).End(xlUp))
If Application.WorksheetFunction.CountIf(.Columns(a + 1)
"*@*") = 0 Then
MsgBox "There are no E-Mail addresses"
Application.ScreenUpdating = True
Exit Sub
End If

N = 0
For Each cell In .Range(.Cells(2, a), .Cells(Rows.Count
a)).Cells.SpecialCells(xlCellTypeConstants)
If SheetExists(cell.Value) = True Then
N = N + 1
ReDim Preserve Arr(1 To N)
Arr(N) = .Cells(cell.Row, a).Value
Else
MsgBox "There is a sheet that don't exist in th
list"
Application.ScreenUpdating = True
Exit Sub
End If
Next cell
End With

ThisWorkbook.Sheets(Arr).Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
.SendMail MyArr, ThisWorkbook.Sheets("mail").Cells(2, a 
2).Value
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Next a
End Sub

When I try to run the macro it creates the workbook for the first shee
it wants to send out and then it gives me the following window:

Run-Time error '1004'

Microsoft Excel cannot acces the file 'C:\Program Files\Commo
Files\System\Mapi\1033\NT'. There are several possible reasons:

-The file name or path does not exist
-the file you try to open is being used by another program.
-The name of theworkbook you're trying to save is the same as the

Would someone know what I am doing wrong?

Thanks a lot in advance

--
Message posted from http://www.ExcelForum.com

0
4/8/2004 8:19:01 AM
excel.misc 78881 articles. 5 followers. Follow

8 Replies
642 Views

Similar Articles

[PageSpeed] 30

I found what I was doing wrong; I had to change the default file settin
in Excel, because it was trying to save the created workbook on 
location that I don't have access to...

The thread can be ignored....

Happy Easter

--
Message posted from http://www.ExcelForum.com

0
4/8/2004 12:09:57 PM
Hi VILLABILLA

Check out your Win.ini file

http://www.rondebruin.nl/sendmail.htm#Problems
See number 2

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


"VILLABILLA >" <<VILLABILLA.14dxhn@excelforum-nospam.com> wrote in message news:VILLABILLA.14dxhn@excelforum-nospam.com...
> Hello! I would like to refer to Ron de Bruin's macro that should make it
> possible to mail sheet or sheets to one or more people, described on
> his I-net site; www.rondebruin.nl/sendmail.htm
>
> I added a sheet "mail" to my workbook and added 6 columns(needed to
> send out the two sheets that I would like to send) :
> The macro looks like this:
>
> Sub Mail_sheets()
> Dim MyArr As Variant
> Dim a As Integer
> Dim Arr() As String
> Dim N As Integer
> Dim strdate As String
> Dim wb As Workbook
> Dim cell As Range
>
> For a = 1 To 253 Step 3
> If ThisWorkbook.Sheets("mail").Cells(2, a).Value = "" Then Exit
> Sub
> Application.ScreenUpdating = False
> strdate = Format(Now, "dd-mm-yy h-mm-ss")
>
> With ThisWorkbook.Sheets("mail")
> MyArr = .Range(.Cells(2, a + 1), .Cells(Rows.Count, a +
> 1).End(xlUp))
> If Application.WorksheetFunction.CountIf(.Columns(a + 1),
> "*@*") = 0 Then
> MsgBox "There are no E-Mail addresses"
> Application.ScreenUpdating = True
> Exit Sub
> End If
>
> N = 0
> For Each cell In .Range(.Cells(2, a), .Cells(Rows.Count,
> a)).Cells.SpecialCells(xlCellTypeConstants)
> If SheetExists(cell.Value) = True Then
> N = N + 1
> ReDim Preserve Arr(1 To N)
> Arr(N) = .Cells(cell.Row, a).Value
> Else
> MsgBox "There is a sheet that don't exist in the
> list"
> Application.ScreenUpdating = True
> Exit Sub
> End If
> Next cell
> End With
>
> ThisWorkbook.Sheets(Arr).Copy
> Set wb = ActiveWorkbook
> With wb
> SaveAs "Part of " & ThisWorkbook.Name _
> & " " & strdate & ".xls"
> SendMail MyArr, ThisWorkbook.Sheets("mail").Cells(2, a +
> 2).Value
> ChangeFileAccess xlReadOnly
> Kill .FullName
> Close False
> End With
> Application.ScreenUpdating = True
> Next a
> End Sub
>
> When I try to run the macro it creates the workbook for the first sheet
> it wants to send out and then it gives me the following window:
>
> Run-Time error '1004'
>
> Microsoft Excel cannot acces the file 'C:\Program Files\Common
> Files\System\Mapi\1033\NT'. There are several possible reasons:
>
> -The file name or path does not exist
> -the file you try to open is being used by another program.
> -The name of theworkbook you're trying to save is the same as the
>
> Would someone know what I am doing wrong?
>
> Thanks a lot in advance!
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
rondebruin (3789)
4/8/2004 12:10:38 PM
The error described in my thread is still occuring. I checked out th
Win.ini file like Ron advised and it's on it's place just lik
Microsoft's site describes.

I thought I resolved the issue after I changed the default fil
location, I did this under Tools-Options-General tab.
It worked fine after I set my Desktop as the default file location, bu
when I close and reopen the file the same error appears again when 
try to use the macro. When I check the default file location I see tha
it still is the path that leads to my desktop like I specified...

I really can't find anywhere what it is that I'm doing wrong...

When I empty the default file location field and replace it again wit
the same path, the macro works fine until I save, close and reopen th
file, then it gives the same error again...

When I press Debug, it shows this part of the macro in yellow:

.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"

So it creates the workbook to send out but it doesn't manage to save i
I guess...

Please help

--
Message posted from http://www.ExcelForum.com

0
4/13/2004 11:44:18 AM
Hi VILLABILLA

Send me your test workbook and i take a look at it

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


"VILLABILLA >" <<VILLABILLA.14ngbs@excelforum-nospam.com> wrote in message news:VILLABILLA.14ngbs@excelforum-nospam.com...
> The error described in my thread is still occuring. I checked out the
> Win.ini file like Ron advised and it's on it's place just like
> Microsoft's site describes.
>
> I thought I resolved the issue after I changed the default file
> location, I did this under Tools-Options-General tab.
> It worked fine after I set my Desktop as the default file location, but
> when I close and reopen the file the same error appears again when I
> try to use the macro. When I check the default file location I see that
> it still is the path that leads to my desktop like I specified...
>
> I really can't find anywhere what it is that I'm doing wrong...
>
> When I empty the default file location field and replace it again with
> the same path, the macro works fine until I save, close and reopen the
> file, then it gives the same error again...
>
> When I press Debug, it shows this part of the macro in yellow:
>
> SaveAs "Part of " & ThisWorkbook.Name _
> & " " & strdate & ".xls"
>
> So it creates the workbook to send out but it doesn't manage to save it
> I guess...
>
> Please help!
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
rondebruin (3789)
4/13/2004 2:01:33 PM
Hi Ron!

Attached you can find the workbook that i tried to use SendMail with.

I very much appreciate your help!

Regards

--
Message posted from http://www.ExcelForum.com

0
4/14/2004 6:26:47 AM
Once again..

--
Message posted from http://www.ExcelForum.com

0
4/14/2004 6:29:35 AM
It doesn't work to attach the file can I send it to your e-mai
@kabelfoon.nl

--
Message posted from http://www.ExcelForum.com

0
4/14/2004 6:30:17 AM
> It doesn't work to attach the file can I send it to your e-mail
> @kabelfoon.nl?

You must send it to that address

Don't (never) attach a file in the newsgroup

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


"VILLABILLA >" <<VILLABILLA.14owgf@excelforum-nospam.com> wrote in message news:VILLABILLA.14owgf@excelforum-nospam.com...
> It doesn't work to attach the file can I send it to your e-mail
> @kabelfoon.nl?
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
rondebruin (3789)
4/14/2004 12:44:21 PM
Reply:

Similar Artilces:

Personal Macro Workbook xls
How do I resolve this error? "Personal Macro Workbook in startup folder must stay open for recording." This occurs when tring to record a macro in a query. Thanks ...

Something strange is going on
Hello; Just installed OFF2004 and tried online registration. First, a message came up (Safari) that the Security Certificate for the site could not be confirmed and that it might be fake (or something to that effect.) I elected to continue. Then I arrive at first registration page which requires that I sign-in to Microsoft Passport (with which I am not familiar.) In the e-mail address field is a default e-mail address; not mine but a that of friend of mine with a @hotmail address. Why is her address there, and not mine or anyone else on my address book? I tried several times and the same ad...

Macro Autostart when opening a file
Hey guys I want to start a (series of) macros start directly when opening an EXCEl-reporting file. I'm certain the must be an easy command - to whom with the knowlegde... Will one of you out there kindly give me a hand on this? Best regards Snoopy Create a macro named Auto_Open() in a regular module. Then in it call the macros you want to have run on startup. Sub Auto_Open() macroname1 macroname2 macronamex End Sub Robert Flanagan http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel "Snoopy" <blaksnm@jjuc.no> wrote ...

Wrong store qty at HQ
Hello, We recently counted inventory at one of our store and then we updated inventory at store level using "Store Operation Manager > Inventory Wizard > Task 150: Change Item Quantity". Once we updated qty at store, we did following (partner gave us these steps): 1) Created 401 at HQ for the store where we recounted all the inventory 2) Once 401 was completed successfully, we ran worksheet 501 for the same store at HQ 3) Once 501 was completed successfully, we ran "Wizards > Inventory Wizard > Task 190: Perform Full Item Count Reconciliation" for the store wh...

Data files lost in drive crash - NO BACKUPs
Hi , I had a hard disk crash and lost all my data on the Data drive. My exchange is intact on another drive. How would I Re-install the Exchange 5.5 over the old without changing any of the config info and Create the new databases?? Any help will be appreciated. Thank you. Postmaster. http://support.microsoft.com/default.aspx?scid=kb;en-us;183266&Product=ech http://support.microsoft.com/default.aspx?scid=kb;en-us;296787&Product=ech In an essence you have to backup what you currently have, install Exchange with exactly yhe same Org and Site names, apply the same Exchange SP...

Macro warning still on
I have Office 2003 - I put a macro in a table then removed it from the Macro drop down. It has definitely been removed as no macros appear in the box. However each time I open the sheet it comes up with a warning to remove or enable macros - but I dont have one in there? Please help as I use the sheet a lot and it is annoying. Thanks in advance. When you record a macro, Excel creates a module in which the code is stored. When you delete the macro, the module is not automatically deleted. There are instructions for finding and removing code on the following page: http://ww...

Need help with the Query. 02-25-10
I need to construct a query base on the requirements below and get stuck. Any help would greatly appreciated. IF OBJECT_ID('Tempdb.dbo.#Temp', 'u') IS NOT NULL DROP TABLE #Temp GO CREATE TABLE #Temp ( LoanId INT NULL, Enterdate DATETIME NULL ) GO INSERT INTO dbo.#Temp (LoanId, Enterdate) VALUES (110810, '2010-02-07 21:30:09.140') INSERT INTO dbo.#Temp (LoanId, Enterdate) VALUES (110810, '2010-01-29 18:31:37.760') INSERT INTO dbo.#Temp (LoanId, Enterdate) VALUES (110810, '2010-01-13 08:09:56.467') ...

Can I automate, or write a macro for this?
Can some one help me automate a process or write a macro to do multiple steps that I repeat several times a day in Microsoft Word? I receive reports (Word docs) that I edit and save and then forward by email to the final recipient. There are about 6 different recipients that may receive these. Here are the steps I do every time after I have finished reviewing/editing the report: 1."Save As". I save it with the default name it had when it was sent to me but I need to save it to separate folder, depending on which final recipient I will be sending it to. 2. "Quick Pr...

Personal Macro Problem
Good Morning All, Using Excel and Windows XP. I want to record a macro and place it in the Personal Macro Workbook. When I click ok I get an error message "Personal macro workbook in the startup folder must stay open for recording" and then another message "unable to record". I've recorded personal macros before and never received that message. Not sure why that message pops up. Also the macro record box doesn't show up on the screen, how do I get it back? Thanks for your help. Michael Maybe personal.xls was disabled... xl2002+ has the abililty to...

VERY URGENT HELP REQUIRED
Hi TO ALL Tech and MSVP: I use to assign item to a site. But today while trying to assign any item to a site, it does not work. I went through Cards>> Inventory >> Item >> Item Qty and Site go to button >> Selected the site >> Save. also i tryed Cards>> Inventory >> site > assign by site id and range of item silent no error when go back to the item realize that it didnt save/assign to the site. There is no error message , also these items does not added into IV00102 table also it exist only one with " " Location code where it sho...

Outlook User Profiles help!
Still need help! Does anyone understand what is happening? >-----Original Message----- >I changed the mesaage tracking prefs (read receipt >notifications, etc.), the change was applied to both user >profiles. I added a signature to one profile it added it >my other profile. I also made one account to default as >plain text and it changed both! It it supposed to work >that way? > >>-----Original Message----- >>Which settings are youi trying to change? >> >>-- >>Sue Mosher, Outlook MVP >>Author of >> Microsoft Outlook ...

Saving the Macro for all workbooks
Hi, I have a code which I have wrtitten seperately which now I to be accessible by all workbooks, I have heard that there is a hidden file where we can save this, but I am not able to find it, can anyone please guide me how to go about it. -- Regards, Pranay Shah Personal.xls. Personal.xls is located in the XLStart directory, and is used to store macros and things that you want to be available to all workbooks, whenever you start Excel. By default, it is hidden in Excel, but if you go to the VBE, you use it as you can with any other open workbook (you don't have to unhide it). ...

Help with too long formula
Hi.....trying to figure out a way to get this formula smaller so that it will fit within excel requirements....and suggestions? =IF(D5="ATL",SUMIF($D$5:$D$40,"=ATL",$G$5:$G$40)/SUMPRODUCT(($G$5:$G $40>0)*($D$5:$D$40="ATL")), IF(D5="BLT",SUMIF($D$5:$D$40,"=BLT",$G$5:$G$40)/SUMPRODUCT(($G$5:$G $40>0)*($D$5:$D$40="BLT")), IF(D5="CHI",SUMIF($D$5:$D$40,"=CHI",$G$5:$G$40)/SUMPRODUCT(($G$5:$G $40>0)*($D$5:$D$40="CHI")), IF(D5="CIN",SUMIF($D$5:$D$40,"=CIN",$G$5:$G$40)/SUMPRODUCT(($G$...

VBA Macro for font styles #2
I'm trying to do this within the same macro, so I shouldn't hav problems with the variable disappearing. All my macro is trying to do is the following: When I press ctrl+shift+O, the style of the selection changes to on thing. When I press it again, it changes to something else. Once cycle through the four options and I press it again, it just gives m back what was there originally and the cycle starts over. The macro is not complicated at all. I guess I just need a functio that returns the font style of the current selection, but I haven' been able to find one yet. Can an...

moving license server ( please answer !!! )
hello i want to change license server role from my DC to a member server which its name is srv2. my DC computer name is srv1. i used active directory sites and services to changing role...I runned this console as administrator ( Run as ). in srv2, i tried to use licensing logging in administrator tools...but i got error which means that my srv2 server tries to query database from srv1. what should i do ? i want srv2 to query itself. please help! ...

Simple macro help #2
I have a workbook with two sheets in it. the workbook is used to keep track of machines in a particular location. on one sheet there is a weekly update and the second sheet is a year to date tracker. what i'm trying to do is on the weekly sheet i want to update the numbers for each location, weekly obviously. on the other sheet i want it to keep track of the yearly amount. for example on the weekly sheet i have 3 machines in memphis and 4 in los angeles. the yearly sheet will read 3 for memphis and 4 for los angeles. then at the end of week two i update the weekly sheet saying there...

Creating a Pivot Table via Macro
I have an Excel workbook that is getting generated from another tool (Quality Center). It has output data on a tab called "FailedBlocked" and another tab that is empty called "TC by Def". I have recorded a macro to create the pivot table. The code Excel creates is: ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ "FailedBlocked!R1C1:R1090C13", Version:=xlPivotTableVersion10). _ CreatePivotTable TableDestination:="TC by Def!R1C1", TableName:= _ "PivotTable1", DefaultVersion:=xlPivotTa...

Problem restoring PST file - Please
Is it possible that using the Windows XP backup program to save my Documents and Settings folder would not save all my outlook data or .pst files. I now have several .pst files and some with current dates and some with old dates, but none seem to be the correct ones. Thanks for any help. ...

Why CELL("format",A1) doesn't return something like "#.##"? ...and other rants
I'm trying to compare the displayed value of two formatted cells. The idea is to detect the equality between 1.2345 and 1.23 when the format is "#.##" without resorting to VBA. This is Excel 2000, SP3. So I decided that this might work (may be wrapped): =IF(TEXT(A1,CELL("format",A1))=TEXT(B1,CELL("format",B1)),"equal","unequal") Unfortunately, CELL("format",A1) returns "F2", which is incompatible with the format string required by the TEXT() worksheet function. The above conditional actually evaluates to "equa...

Money has the wrong stock ticker symbol
Money 2005. Yesterday, I bought a new stock, AGG. On the next update, Money noticed I had a new stock in my portfolio, and gave me the usual popup message asking if this is a new stock. I said yes. I already had this symbol in my "watch" list, so Money asks a follow-up question about which I want to keep. Unfortunately, due to some odd bug, Money ignored my answer, and kept presenting the same dialog box over and over. In the end, the only way I could come up with to get Money out of its loop was to manually add a "dummy" entry to my portfolio (I chose the symbol &quo...

custom macro needed
I work for a company in Hertford now. We are an accident management company as part of our services we provide our fleet customers with weekly, biweekly, monthly or even daily reports. one of the pieces of information in these reports is the downtime which is how long the vehicle has been in the bodyshop. I have attached an working example to this to this email it uses an excel formulae to calculate the difference between the 2 dates. now then what I want to know from you is how to improve on this formulae. I need it to not only work out the difference between the 2 dates but if a ons...

Macro #53
Is it possible to have a macro in excel go out to the internet to pull information into a spreadsheet? Thanks Mike ...

InternetReadFile - CString
Hello, I have to open remote files on different server (all files are ascii text). My code look like that : (Pseudo-code) CString s; h = InternetOpen("Rien",INTERNET_OPEN_TYPE_PRECONFIG,NULL,NULL,0); http = InternetOpenUrl(h,"http://Path",NULL,0,0,0); InternetReadFile(http,s.Buffer,sizeof,&byteread); m_mystring = s; UpdateData(FALSE); It works only with the files who are 'dos' formated, the others can't be display in an CEdit box. What can i do ? Is there a function to directly convert unix->dos ? How can i ...

HELP! A project management templete
Hi there, I need a project management template, like the ones listed on the microsoft site, but for microsoft excel, not the project management program...any ideas? D Hi try: http://j-walk.com/ss/excel/files/timeline.exe -- Regards Frank Kabel Frankfurt, Germany D wrote: > Hi there, I need a project management template, like the ones listed > on the microsoft site, but for microsoft excel, not the project > management program...any ideas? > > D ...

Alternate to while loop in threads
Hello, I am new to Mulithreading. In my project I am working with multiple threads. Since I haven't worked with threads before, I am facing many hurdles. Can any one please guide me or give some hints. I am explaining in brief about the threads used by me in my project. 1st thread: Filter Thread Is started at application start up. Filters each line read from file( Log data is read from file) I have used CCriticalSection and CSingleLock to lock the data access here as this LIST is accessed in Display thread. Adds the data to the LIST from top. This thread has to run continuously till t...