Macro Help - Jacob Skaria has previously been helping

Hi,

I had alot of help yesterday from Jacob with the following macro, but am 
getting a 'run time error 13' when trying to run the macro, and i cannot see 
why.

Any help much appreciated

Sub OLApp()

Dim objOL As Object, objApp As Object, lngRow As Long

Set objOL = CreateObject("Outlook.Application")

For lngRow = 9 To Cells(Rows.Count, "A").End(xlUp).Row
If Range("E" & lngRow) = "" Then
Set objApp = objOL.CreateItem(1)
With objApp
..Subject = "Change Password for system" & Range("A" & lngRow)
..Start = Range("B" & lngRow)
..ReminderPlaySound = True
..Save
End With
Range("E" & lngRow) = "Done"
End If
Next
Set objOL = Nothing
End Sub

0
Utf
4/28/2010 7:11:01 AM
excel.misc 78881 articles. 5 followers. Follow

16 Replies
2664 Views

Similar Articles

[PageSpeed] 36

Please ignore, i have worked it out.

Thanks
0
Utf
4/28/2010 7:20:01 AM
Check your other post.

I dont see any reason why that should give an error. 
-- 
Jacob (MVP - Excel)


"Dan Wood" wrote:

> Please ignore, i have worked it out.
> 
> Thanks
0
Utf
4/28/2010 7:23:01 AM
I do have another question though.

Is there a way to automatically clear the colum 'E' if something in colom 
'C' is changed?
0
Utf
4/28/2010 7:27:01 AM
Sorry i was being stupid again like yesterday! I simply had to change the 
line .Start = Range("B" & lngRow) to the correct column!

Sorry!
0
Utf
4/28/2010 7:32:01 AM
How does this work? How do i make it as an event macro?
0
Utf
4/28/2010 7:56:01 AM
copy it as written into 'thisworkbook' section 
(without the dashes lol)

its an event macro because it triggers on an event - in this case 
worksheet_change so every time that event happens this code will run, 

it checks if anything in column c changed (intersect(target,[C:C]) is 
nothing) and if it has clear columns(5) (which is e)

"Dan Wood" wrote:

> How does this work? How do i make it as an event macro?
0
Utf
4/28/2010 8:23:01 AM
It doesn't seem to be working at the moment. I have copied it into the This 
Workbook section, and moved the other macro into the Sheet1 section.

Is this something obvious i am doing wrong? Do you need to see both bits of 
code?
0
Utf
4/28/2010 8:59:01 AM
yeah, sorry - I responded to your question without looking at micky's code 

the following works for me (when placed in thisworkbook)

Private Sub Workbook_sheetChange(ByVal sh As Object, ByVal Source As Range)
    If Application.Intersect(Source, [C:C]) Is Nothing Then Exit Sub
    Columns(5).ClearContents
 End Sub

hth
RegMigrant
"Dan Wood" wrote:

> It doesn't seem to be working at the moment. I have copied it into the This 
> Workbook section, and moved the other macro into the Sheet1 section.
> 
> Is this something obvious i am doing wrong? Do you need to see both bits of 
> code?
0
Utf
4/28/2010 9:15:01 AM
That works to clear the entire column, but isn't quite what i need. 

The sheet is to store passwords, then there is a macro to add appointments 
into outlook for the date the password needs changing. Therefore, in column 
'A' starting at row 9 there is a list of system names. Then in column 'D' 
starting in row 9 again is the date that the password expires, and this is 
added to the calendar. Column 'E' is simply there as a way to stop duplicate 
entries in the calendar. So what i want is for example if the system in A12 
password expires, it will then be changed and the new expiration date will be 
input into D12, so i only want E12 to be cleared.

Is there some way to do this, or is it to complicated?

Thanks for your help
0
Utf
4/28/2010 9:35:01 AM
Well its possible but still unclear -

How does data get into E2
what data is it - does it need to be preserverd, is there a formula or 
validation to worry about?


"Dan Wood" wrote:

> That works to clear the entire column, but isn't quite what i need. 
> 
> The sheet is to store passwords, then there is a macro to add appointments 
> into outlook for the date the password needs changing. Therefore, in column 
> 'A' starting at row 9 there is a list of system names. Then in column 'D' 
> starting in row 9 again is the date that the password expires, and this is 
> added to the calendar. Column 'E' is simply there as a way to stop duplicate 
> entries in the calendar. So what i want is for example if the system in A12 
> password expires, it will then be changed and the new expiration date will be 
> input into D12, so i only want E12 to be cleared.
> 
> Is there some way to do this, or is it to complicated?
> 
> Thanks for your help
0
Utf
4/28/2010 10:04:01 AM
I will try to describe as best as i can!

Starting in Column A9 downwards there will be a list of system names that 
will updated by the user. From B9 downwards is the new password the user 
enters. C9 downwards is the date the user changed the password, which again 
is enetered by the user. Cell D9 will be locked, but is basically the formula 
=C9+30, and will display the date the password will need changing. This date 
is used by the macro to add the appoinment into outlook calendar. Cell E9 is 
filled in automatically by my original macro which, once the appoinment has 
been added, is filled in with 'Done'.

So when cell C9 date is changed, i want cell E9 to clear, so that when i run 
the macro to add appoinments, which will be as a button, it will spot that a 
cell in column E is empty and add that appointment
0
Utf
4/28/2010 10:31:01 AM
I suggest you modify your macro to clear column e when it sets up 
appointments and column e says 'done'

RegMigrant


"Dan Wood" wrote:

> I will try to describe as best as i can!
> 
> Starting in Column A9 downwards there will be a list of system names that 
> will updated by the user. From B9 downwards is the new password the user 
> enters. C9 downwards is the date the user changed the password, which again 
> is enetered by the user. Cell D9 will be locked, but is basically the formula 
> =C9+30, and will display the date the password will need changing. This date 
> is used by the macro to add the appoinment into outlook calendar. Cell E9 is 
> filled in automatically by my original macro which, once the appoinment has 
> been added, is filled in with 'Done'.
> 
> So when cell C9 date is changed, i want cell E9 to clear, so that when i run 
> the macro to add appoinments, which will be as a button, it will spot that a 
> cell in column E is empty and add that appointment
0
Utf
4/28/2010 11:11:02 AM
Dan Wood,
You are best off using Mickey's code, or a variation of it, in the worksheet 
code module of the sheet you are interested in.  To put any
Private Sub Worksheet_event()
code into a worksheet, simply choose it and right-click on its name tab and 
choose [View Code] from the list.  That brings up that specific sheet's code 
module for use.

If you use the ThisWorkbook object code and the
Private Sub Workbook_SheetChange()
event, the code will work on EVERY sheet that a change is made on as 
indicated in the code unless you test to see which sheet had the change 
within the code.

"Dan Wood" wrote:

> I will try to describe as best as i can!
> 
> Starting in Column A9 downwards there will be a list of system names that 
> will updated by the user. From B9 downwards is the new password the user 
> enters. C9 downwards is the date the user changed the password, which again 
> is enetered by the user. Cell D9 will be locked, but is basically the formula 
> =C9+30, and will display the date the password will need changing. This date 
> is used by the macro to add the appoinment into outlook calendar. Cell E9 is 
> filled in automatically by my original macro which, once the appoinment has 
> been added, is filled in with 'Done'.
> 
> So when cell C9 date is changed, i want cell E9 to clear, so that when i run 
> the macro to add appoinments, which will be as a button, it will spot that a 
> cell in column E is empty and add that appointment
0
Utf
4/28/2010 11:29:01 AM
Ok i will try that. Is there a way then to get cell E9 to fill when cell C9 
is changed? 

Thanks for all your help with this
0
Utf
4/28/2010 11:37:01 AM
Try the below..

Select the sheet tab which you want to work with. Right click the sheet tab 
and click on 'View Code'. This will launch VBE. Paste the below code to the 
right blank portion. Get back to to workbook and try out.


Dim varData As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then
If varData <> "" And varData <> Target.Value Then
Target.Offset(, 2).ClearContents
End If
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
varData = Target.Value
End Sub

-- 
Jacob (MVP - Excel)


"Dan Wood" wrote:

> I do have another question though.
> 
> Is there a way to automatically clear the colum 'E' if something in colom 
> 'C' is changed?
0
Utf
4/28/2010 11:45:01 AM
nice !


"Jacob Skaria" wrote:

> Try the below..
> 
> Select the sheet tab which you want to work with. Right click the sheet tab 
> and click on 'View Code'. This will launch VBE. Paste the below code to the 
> right blank portion. Get back to to workbook and try out.
> 
> 
> Dim varData As Variant
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Column = 3 Then
> If varData <> "" And varData <> Target.Value Then
> Target.Offset(, 2).ClearContents
> End If
> End If
> End Sub
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> varData = Target.Value
> End Sub
> 
> -- 
> Jacob (MVP - Excel)
> 
> 
> "Dan Wood" wrote:
> 
> > I do have another question though.
> > 
> > Is there a way to automatically clear the colum 'E' if something in colom 
> > 'C' is changed?
0
Utf
4/28/2010 1:29:02 PM
Reply:

Similar Artilces:

VBA Switching Between Macros
Hi, I have a lot of macros in my spreadsheet. And about 50% of the macros have variables that are passed through them, so for example the macros look like Macro1(x,y,z) rather then Macro1(). But usually to go to the code in a macro I look up the macro in the list on the toolbar. Bur these macros don't show up. Is there an easy way to go from one macro to the other's code. It is getting to be a pain searching through the different "modules" Thanks for your help What exactly is the problem? You can't see them in the list, but if they have parameters it presumabl...

Spreadsheet help!!!
I have a workbook with 2 worksheets. Information and Table in the worksheet Information, cell S24 if the number is greater than or equal to $100,000 but less than $149,999 then I'd like it to put in cell S25 the number $3000. Also if the number is between $150,000 to $199,999 i'd like it to put in $3500 in the S25 cell. Any help would be highly appreciated... Thanks alex -- alexm999 ------------------------------------------------------------------------ alexm999's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=4918 View this thread: http://www.excel...

Unable to add a new contact in CRM 4.0
Hi all Unable to add a new contact in CRM 4.0 as it errors out saying "Object reference not set to an instance of an object". Here is the event log information. Web Service Plug-in failed in OrganizationId: 01f0210d-6bf6-4c27-8919-00eccab73e2d; SdkMessageProcessingStepId: ab0fa3e5-3ceb-dc11-8840-0003ffbb159c; EntityName: contact; Stage: 50; MessageName: Create; AssemblyName: AlertCRM40Plugin.CRM40PostPlugIn, AlertCRM40Plugin, Version=1.0.0.0, Culture=en-US, PublicKeyToken=4406fa73b5445f20; ClassName: AlertCRM40Plugin.CRM40PostPlugIn; Exception: Unhandled Exception: System....

need help making chart
I have a data table which im trying to produce a some charts from but cant seem to get it right. bit hard to explain without seeing the table so I have provided the dummy workbook im working on at www.darkcity.nildram.co.uk/test.xls in this example ive got the big data table, and the type of chart im trying to produce form it us just below. the problem is ive had to manually create another smaller table under the big one in order to get the chart to look anything like what its meant to. i really need to have 3 charts produced based on whats in the big table if possible, I need charts by we...

Please help!!!!
I would like to know how to plot multiple sets of data on a scatter graph for example i have 3 sets of data for three types of lighting systems, red, green and blue i want to plot the voltage againt the current, and i want all the data on one graph so i can compare them, how do i do it? Frances I suggest you try the following. Plot a scatter graph as normal with your first set of data. Then to add more data to the scatter graph... Right click on chart and select <Source Data> and select the <Series> tab. Select <Add> in the series dialog box and type in the relevan...

HELP! how to turn off synchronization log messages?!
I have outlook XP on win2k, and my deleted items folder is being filled by "synchronization log" messages at the rate of 1 per minute. Where is a setting in outlook where I can disable/change that? Thanks! Rather than trying to turn off the symptom, perhaps you should look at the logs to see what the problem is? Aaron wrote: > I have outlook XP on win2k, and my deleted items folder is being > filled by "synchronization log" messages at the rate of 1 per minute. > > Where is a setting in outlook where I can disable/change that? > > Thanks! There is...

Help
CRM 3.0 refuses to install on my SBS 2003 R2 premium server. I was encountering the known issues with CRM 3 on SBS R2 using the CDs that came in the Action Pack subscription so I downloaded the updated CRM CDs from http://www.microsoft.com/downloads/details.aspx?FamilyID=7d418781-69ad-422d-92fa-87fdb2538e2c&DisplayLang=en This copy gave me a different set of problems. The default setup mode pops up a couple errors. The first one is the lack of full text search so I installed that and ran the setup again. The next error was: "The edition 'Workgroup Edition' of the specified SQL...

How can I find my old macros?
I currently have Microsoft 2007 installed on my computer. I created a ton of macros/autotext in Word 2003. I do not know a lot about computer language, so answers that tell me "where" to go and not "how" to get there do not help me. Please tell me, step by step, how to find my old macros and how to transfer them into my new Microsoft 2007. I'm having serious trouble with adjusting to Microsoft 2007. Those macros and AutoText entries should have been preserved if you installed Office 2007 over Office 2003, but they will not have been transferred to th...

Macro Replace Column A with Column B
Howdy, I've got a list of 250 some odd abbreviations and full codes for excel data. I want to run a macro that will search for A1, then replace it with the value in B1, then A2 with B2, and so forth until the end of data in column A. Any ideas on how I could do this? Btw, is it possible to store the conversions in the macro workbook, so it isn't "lost". IE, could I make a sheet1 in personal.xls, then have the macro check personal.xls, sheet1, column A, then replace with personal.xls, sheet1, column B? Hope this makes sense! Thanks in advance! Austin bump I don't qu...

macro for copying file
How to write a macro which copies a file in one loacation (source location) to other folder (destination). I used the function FileSystemObject.CopyFile "c:\mydocuments\letters\*.doc", "c:\tempfolder\" but it returned error. Can any one help me. Regards, Joseph My bet is you don't have a folder named "c:\mydocuments\letters" or you don't have a folder named "C:\tempfolder" or you don't have any *.doc files in that source folder. (c:\mydocuments could be c:\my documents (with that space character).) But you could check each before ...

Upgraded both of my macs to Office 2004 and wish I could have Office x back! Any help?!?!
Hi, I am running a G5 Imac 1.8Ghz and a G4 Powerbook 1.5GHz and have been happily running office X on both machines for as long as I've owned them (1 and 2 years respectively) Before the Pbook, I also had an I book running Office X for 18 months with no troubles. I just installed Office 2004 2 weeks ago, and am consistently noticing, on both machines, serious system slowdown over the course of the day. To the point where, by mid-afternooon, my system needs to be restarted. The symptoms are identical on both macs (naturally the Pbook runs even slower than the G5 as these problems advan...

How Does One Limit Outlook Help To Outlook?
If I need to look something up related to Printer I don't want the help program to tell me how to "Find and install printer drivers for Windows Vista" followed by "Find printer drivers for Windows XP". I entered Printer into Outlook help and the first ten results were: Add or remove a printer Change your default printer Find and install printer drivers for Windows Vista Find a printer manufacturer's Web site Find printer drivers for Windows XP Get the version number for your Office program and information about your computer Change an ink or ton...

HELP
When i'm on Outlook, how can i know which user account i use? but when i start Microsoft CRM, i know which user i am because i must log in! Could you tell me, what is the problem? I wish you understand what i said Thanks to reply ------=_NextPart_0001_42E90E06 Content-Type: text/plain Content-Transfer-Encoding: 7bit Hi and thanks for posting your question. When you launch Outlook, it will use the credentials that you use to log on to the computer. This will be a domain account, not a local machine account. When Outlook is launched for the first time, it will create a profile fo...

"Operation failed. Object could not be found." Outlook 2003, HELP
My Outlook 2003 testing on POP 3 indicates everything is okay. But when ever I go to send or receive anything it always show the "Operation failed. Object could not be found." Need help because Microsoft sure as heck doesn't have any solution for this problem. Thank you You've provided no information that would permit anyone to answer. State your how you configured your information store. State whether Outlook ever worked for you and if so the steps you used to corrupt your Outlook profile. -- Russ Valentine [MVP-Outlook] "Bushie" <Bushie@discussions.mic...

Problem seting-up Regression Macro
I am trying to automate running of regression macros in Excel. I used the "record" method to create one. But everytime I run the "recorded" regression macro, I get the error message: "Run-time error '1004': 'ATPVBAEN.XLA' could not be found. Check the spelling of the file name, and verify that the file locations is correct. If you are trying to open the file from your list of most recently used files on the File menu, make sure that the file has not been renamed, moved, or deleted." I have searched my hard drive and this file (ATPVBAEN.XLA)...

Error 3045 on an MDE file
Hi I hope you can help me with this it is driving me nuts. I've created a database which I needed to give multiple people access to but didn't want them messing so I created an MDE file. I ensured that the MDB file was set to shared, not compacting and set to run users not owners permissions before I converted. However, if one of the users goes into the database, it does not create and LDB file and if anyone else tries to get it, the error message: Could not use <name>; file already in use. (Error 3045) appears. Some of the tables in the MDE files were linking to and MDB fi...

Outlook 2000 error, help
I'm not real familiar with outlook, we use outlook express in our office however new person wants to use "outlook" for email because he wants to transfer the contacts he had from his old company. We have office 2000 here and he can use outlook, that's fine. The problem is, I've imported his contacts and now when I open the Outlook program I get the following error: "A program is trying to access email addressess you have stored in outlook. Do you want to allow this? If this is unexpected, it may be a virus and you should choose no. Allow access for 1, 2, 5...

need help calculating intermediary values
I'm not well enough versed in spreadsheet use to calculate some intermediate values I need. I would be greatful for anyone who can offer a formula or suggestion. I have 1 guidline column, numbered 1-30. Then 2 other columns, A & B are beside it. The values in A & B change as they go from 1-30, they change at different rates. I have some intermediary values at given points but need to calculate what the values in between would be, based on the fixed values at 1, 4, 8, 15 and 30. Below, I have shown the layout as best I can without including an attachment. Thanks. chris@cjalexander...

Date Function Help
I need some assistance writing a formula that will count how many dates (in a list of dates in m/d/y format) are in each month. So... Column A [1/1/08, 2/7/08, 10/19/08, ...] Column B [Months, Jan, Feb, Mar, ...] Column C [Counts, {count of dates in January}, {count of dates in February}, ...] Make sense? The obvious answer would be to use another column to extract the month values and then just perform a COUNTIF function. But I can't use an extra column. Any help is appricated. Thanks, Shelton Try this in C1: =3DSUMPRODUCT(--(MONTH(A$1:A$100)=3DROW(A1)),--((A$1:A$100)<>"...

OT : Reading Help 2
Does anyone know of a free HTML Help 2 reader? Microsoft has completely redone their docs, so that for most help topics it's all or nothing: Install .Net and the entire Windows SDK -- which comes with Help 2 files and the necessary reader software -- or get no docs at all. That's about 1GB of stuff that I neither want nor. (Since most of it is just .Net docs.) I just want access to the latest versions of things like WMI help that used to come as independent downloads containing a CHM file. On Thu, 25 Mar 2010 15:25:00 GMT, mayayana wrote: > Does anyone kno...

HELP: retreiving forgotten passwords from Outlook
I've got Outlook 2003 and got a POP3 mail account set up. However I want to do a fresh installation of Windows and will lose my account details. I do not remember my password and was wondering how can I retreive my password for the "Logon Information" and also "More Settings | Outgoing Server" passwords. Since the passwords are in ****** format. I assume the passwords are stored in Outlook files encrypted right? KevinGPO, you wrote on Thu, 15 Dec 2005 09:20:48 -0000: > I've got Outlook 2003 and got a POP3 mail account set up. However I want to > d...

Sent e-mail disappear!!!!!!!!! Can you help
. Reply (E-mail) Forward (E-mail) Subject: Can anyone help with Outlook 2002 Problems From: "robin hickey" <robin@e-s-p.eu.com> Sent: 3/3/2004 9:38:36 AM Hi, I have Office XP pro, which I upgrated to because my other installation of 2000 still had the following problems... When I send an e-mail it is received by whoever it is sent to, and it appears in the outbox before it is sent, but then just disapeers without being able to be found anywhere?? Is this a virus? Also, when creating my own letterheads for two different e- mail accounts, but using one...

Link Cells (Read for more info) -- HELP PLEASE
Ok, this is for more advanced Excel users. Here's the issue. Lets say I have two sheets in Excel, S1 and S2 respectively. Let's say in S1, if I type a 4-digit number IN COLUMN A, which is actually an ID number for a person, I want their name to automatically appear in COLUMN B from a list in S2. This is how S2's list is set up... A1 has a person's ID #, and B1 has that same persons name. A2 has a different person's ID #, and B2 has that person's name, etc... So when I type the ID # in S1 in column A, it will lookup in S2 and match that person's nam...

Previously permitted folders will not delete
Using Outlook 2000, I have found that when User A has been given permission to access User B's folder(s), then User B removes the permissions for User A to access the folder(s) any longer, User A's Outlook setup will still show User B's folder(s) in their list of accessible folders when in option File => Open => "folder". Is there a way to delete the no longer accessible folder from the list? Thx. Check out http://support.microsoft.com/default.aspx?scid=kb;en-us;292024&Product=out -- PATRICK REED [Outlook - MVP]~~~~~~ -Microsoft Certified Professiona...

Help Needed: "IF" formula/logic too limited
I need to determine a value that is predicated upon the selections of about 6 or so drop down menus. It'd be General Text within drop-downs but final value is an Accountant price Basically if someone selects Option #1 out of Cells A-G's drop down menus, then the value of H1 = a cell on another page that fits the category of 1- 1-1-1-1-1 from the drop down menus. I don't know how to do this. I imagine it's possible but have no clue. Your help is greatly appreciated. From what you say, the meat of this problem lies in the layout/format of the data you have in this "...