Cannot get Sheet to automatically hyperlink to designated email within the sheet. HELP!

Thank you So much, I have been struggling with this for sooo long. I did find an automatic email VBA that may work but don't know how to tweak it or insert it into the communication Log Sheet. I can attach it for you to have a look. I tried it and it does work in another sheet, unfortunatley it is not customized for my sheet or needs and it automatically emails on Opening, I would need it to do so upon closing an email, that way the latest notes entered are attached to the email. Now there is a VBA in that sheet that keeps auto-locking after you populate in the C- column. If you want to teporarily cut that particular vba out while you work out the automatic email part, feel free. Otherwise everytime you do something to it, it locks you out and you have to keep re-entering the "Password" to continue working on it. Let me know how I can assist, and here is the VBA i found for emailing as well. Maybe you can tweak it to make it function properly in my sheet, ("Communication Log").

thanks again, dan

Private Sub Workbook_Activate()

 Sheets("Training Summary").Activate

 Dim OutApp As Object
 Dim OutMail As Object
          
    Dim iArray(1 To 8) As String
    c = 1
    For i = 3 To 10
        Select Case Cells(i, 2).Value
          Case Is <= 30
            iArray(c) = Cells(i, 1)
            c = c + 1
          End Select
      Next i
      
  strbody = "These Items Are Due In 30 Days Or Less" & Chr(13) & Chr(13) & _
            iArray(1) & Chr(10) & _
            iArray(2) & Chr(10) & _
            iArray(3) & Chr(10) & _
            iArray(4) & Chr(10) & _
            iArray(5) & Chr(10) & _
            iArray(6) & Chr(10) & _
            iArray(7) & Chr(10) & _
            iArray(8)

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .To = Range("F1")
        .Subject = "Communication Log"
        .body = strbody
                
        If UCase(Range("F2").Value) = "YES" Then
            .send
        Else
            .Display
        End If
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

See in this VBA where it says,

strbody = "These Items Are Due In 30 Days Or Less" & Chr(13) & Chr(13) & _
            iArray(1) & Chr(10) & _
            iArray(2) & Chr(10) & _
            iArray(3) & Chr(10) & _
            iArray(4) & Chr(10) & _
            iArray(5) & Chr(10) & _
            iArray(6) & Chr(10) & _
            iArray(7) & Chr(10) & _
            iArray(8

I need this to be changed in my sheet, in column "C" to show the contents of whatever text notes get typed in by my project managers and also the next row, then the next, etc. I guess you call that a "dynamic range"? not sure. I did creat all the sheets in the workbook and love, LOVE excel! I am darn near obsessed with excel and think it should be used for everything. . I am still very novice with it though and it takes quite awhile to put some of those sheets together and get them to behave properly. I have never messed with VBA. Completely foreign to me, but see that it is very powerful. I will be learning how to use it though. Very Cool stuff.
You are obviously "the Man" when it comes to writing this stuff. Very Impressive.

thanks again for your help. I am so excited to have you on this workbook. I know you are going to notice the rows showing are like 362 or so and not rows 3,4,5, etc. That's because I had to hide them because they would lock after entry and I couldn't delete them. That vba for auto-locking is exactly what I need so no one can go back and alter the notes without password or admin. rights but as I have been messing around with it to try to make it work properly, I have created a real mess. I don't know how to get it back to the first rows and set the range of active rows from the top of the sheet starting with row 3 and going down to at least 200 rows or the whole sheet if that is easier. Sorry for the mess I made of the Sheet "Communication Log". If you need to start a new sheet from scratch, I understand. I can just ad the info. for Representative, Customer, date, etc. easy enough. Thanks again for your help.

No, the whole sheet I guess. See, we do insurance, disaster recovery work. What I was intending to do with this sheet is manually email it the the customers Insurance agent and adjuster so they can see the daily progress of there customers restoration project. Maybe you have a thought on that now that you know what I am needing the sheet emailed to me for. What do you think? I suppose the whole sheet with the last entered note would be good show the story line of the entire job as it progresses. If I were an adjuster, agent or the customer, I guess that would look better, that way they will see where in the big scheme of things the last note applies. Make sense? So I guess the whole sheet.

thanks,

Just to clarify, I do want the sheet to be automatically emailed to me (after the last or most recent note has been entered). Upon closing the sheet, it will get automatically emailed without confirmation or the option for one of my employees to see a box pop up on there screen saying the sheet is getting emailed. Then in turn I can manually forward it to the interested parties. It also allows me to see whats going on with all of our projects.

thanks,

Yes Please. Each time a new note is added. Not sure if you saw my earlier note about the rows being hidden. If while you are in the sheet and know how to reset the entire sheet to start from row 3 and apply vba and cleared cells throughout the entire sheet, that would be great. After the sheet is working properly, I will save a master template that is "clean" and use a few copies for training purposes.

thanks,

This is the new attachment with some vba code for automatically emailing the sheet named "Communication Log". It does not work. It needs some pro's TLC.

Nice Work! A question..... Everything works perfectly however, a message box pops up when the note in column C is navigated away from and the next row is ready to populate advising an email is being sent in my behalf. Can we make that box not pop up? also, if the employee accidentally hits the enter key during the process of typing his or her note, the incomplete note will be emailed. Can we make it so the automatic email will not be sent until the sheet is trying to be closed by the employee? And finally, the attachment concept may not be allowed by certain Insurance Companies firewalls and therefore the attachment and email in its entirety will not make it to the recipient. Can we make the body of the email show the "Representative", Date (in column B) and the note itself? If we could pull these things off, you would be amazing! You have no idea how much we appreciate your expertise and help.

thank you,

If the HTML in the body of the email is easy enough to do, then lets go ahead with that. I imagine as you tested the sheet yourself, you saw how the email looked when you received it. It works perfectly, I am just looking at it from the recipients eyes. What does the email say to them when they open it, you know what I mean? The presentation of the email has to be fast, to the point and get the message across quickly and easily as adjusters are impatient and very busy by nature and will not spend any time at all messing around with clicking on attachments or opening spreadsheets etc. That's why I am leaning towards simple, easy and fast updates for them to see with just one click of there mouse to open the email, see whats going on with there client and move on to there next order of business. They will reject it if it's a pain to deal with the email from us. We will be hitting them hard with alot of emails showing the updated status of their clients Projects.
As far as "until the sheet is trying to be closed by the employee", means if we could make the automatic email not send until the employee closes the workbook. You currently have it set to automatically send the email when column see is navigated off from, am I right? Maybe I'm wrong on that but as I said in my last reply, If you picture yourself typing a note in a given Row in column C and you accidentally hit the enter key and it moves you off the row you were typing in, then the automatic cell lock kicks in and,,, Boom, there goes the automatic email and I will be getting the incomplete or accidental email. The employee will then have to go to the next Row down and finish or correct his or her note and then it will send again. It would be much better if we could set the vba so the automatic email does not kick in until the workbook is closed. Make sense?

thank you,

I forgot, don't worry about the Outlook issue. You are correct in the SMTP/Network mess. That annoying box popup can be dealt with. I might be able to find a solution in Outlook for making that not pop up. What do you think. Have you heard of a way or know if it's possible to configure Outlook to not show that message box?

Hmmmm, no I did not receive an email. Was it from the worksheet? I am about to open the worksheet and test it. I will let you know here shortly.

Thank you,

No, it's not working. When I click on the name in the dropdown list in column "A" it should automatically populate Column "B" with date as it skip over it and has jumped staight to column "C" where we write our note. It stays on column "A". I have one other thing I want to do that I feel should be very easy to do. In Row 1 where you see the word "Customer:" then in B1 where you see Test Data (where the customers name will be), I know we are adding that in the subject line for the adjusters to see who the customer is but they have so many customers they are taking care of, they usually will look up that customer in there computer files. I know they search customers by Claim #. What I was thinking is to insert a column between column B and C which would allow adding the claim # in the next cell to the left of where the note will go. I guess you will need to tweak the code that makes the auto populating of the date in column "B" anyhow, maybe we could add the columns to include the claim Number. I can jump in there and ad those real quick for you to see what I mean. I will re-attach the workbook. Cool?

This is the copy I adjusted, showing the columns for claim #. I would like to add them to the subject line. So the when the recipient receives the email, the subject line will read (in the workbook I just posted for test purposes), Mrs. Test, claim# 33-D236-951 HP. That way the adjuster or agent can quickly see the claim# right in the email subject line and use it to locate that customer in there server and maybe even copy and paste our log notes directly to there file. Lastly, would it be easy enough for me to add an additional email in below my email in the worksheet in the future if the need arises or will that mess up the whole sheet?


thanks, for your help.

Can we keep your email address in there for a minute to see if it will send to both of us?? That is actually what I was just asking about in my next to last post this evening. That would be a great option for when I get a marketing agent, then I can add him or her to the automatic email list for marketing purposes as they will be abreast of what is going on with an agents customer so when they go call on them, they will both know what the latest updates are for that customer.

When I go to close the the workbook after writing my note, I am getting a "Runtime Error 287: Application-defined or Object-defined error". I did notice when I opened your attachment it is written or at least saved in Excel 1997. I am running 2010 version. I also have to hit the "Enable Content" button in the far upper message bar that comes up above the worksheet. You think that has something to do with the error? Although it was working fine on the earlier attachments you and I were working with. Maybe we should go to the upper portion of this thread and re-open one of those attachments and see how the functionality is, for a comparison. ??

The debug results show a yellow highlighted area over .send it is the 3rd code item up from the END of the bottom of the code window and its in the module "Email_Worksheet".

I have copied and pasted the area of the code that shows yellow highlighting. I of course will not show highlight in this message but I have wrapped it in U brackets. Hope this helps.

.HTMLBody = Body
                        .Send
                    End With
                
        CloseOutlook InstanceInfo
        
End Sub

My mistake, when the message posted, the U brackets disappeared and the error area is underlined. it is " .send". thanks,

Yeah, that's me. I just checked my email (dan.hill@mail.rainbowintl.com). I have no new emails. Hmmm, how long ago did you send it?

I did notice a debug box popped up when I opened the worksheet from you. Not sure what it is looking for. Just FYI. You will notice in the cells where I have the customers name (Mrs. Test), and the cell with the claim#, has a formula for looking at the first sheet in the workbook named "Claim Loss Report". That is the correct function for populating all the sheets automatically when the "Claim Loss Report" is filled out by our office staff. The whole workbook will be fully automated. Very nice! Very efficient as well. We will be handling a large volume of clients and I am building a business model that will be pretty much paperless. My last quest is to build a master search sheet that will be able to find this workbook you and I are finishing which will be residing in apox. 6-8 seperate folders. One for each Insurance company and each customer gets there own workbook. I have posted a thread on that today as well. I have some code going on it. It the search button at the top of the page works perfect, but it will only search for data in that sheet. I need to figure out how to have it search for a customer in whatever folder they reside in and preferably it not only will locate the workbook in the folder it is in but actually take you to it and the best case would be to take you straight to the communication log. Wow huh? That one is going to be a bear. (I think anyway) I have spent the last 4 days solid, all day long working on just that sheet. Very frustrating.

The claim number can stay at the top. It is there only to be added to the Subject of the email, that is all. As for the emails, I don't have additional ones I want to add right now. Would it be safe and easy enough for me to add a couple below mine in the sheet in the future and then go into the VBA code and add it after my email? You would of course have to tell me how to enter additional email in the code. Like commas, spaces, ampersands, funny symbols etc. You know,,,, vba code stuff .

thank you,

I posted the reply to your question of where is the error ocurring, which module etc. in the end the reply to one of your earlier posts. I am messing up this thread, I apologize for the mistakes. I am not sure if you got my replies due to that. I will pay better attention next time. Let me know if you still got my answer to your last questions.

thank you,

What is your email address again? I will enter it in F1 on the sheet you posted for me in this thread yesterday. I will enter a note and hopefully it will automatically email to you. Will that be ok?

thank you,

I found your email in the other sheet from earlier and just tested the sheet you modified from yesterday. Please let me know if you got it. I still have not received an email from your new sheet that you attempted to auto-send me. Not sure what is going on with it.

thanks,

I do have admin rights. The other sheets from yesterday work fine so I'm not sure that's the issue. Hmmm, I will try the one from yesterday again and let you know in just a minute.

thanks.

What is your email address again? I will enter it in F1 on the sheet you posted for me in this thread yesterday. I will enter a note and hopefully it will automatically email to you. Will that be ok?

thank you,

So it works! And looks great. I am not sure why this version does not want to send if Outlook is closed. The one you did yesterday sent regardless. If we could get that straight and add the claim # and a short instruction note for me to be able to add a couple more email recipients in the future, I think we will have it. I will just need a clean sheet again, starting with Row 3. I did notice the notes in rows further down all of a sudden were in bold in column B where the Date and Time is. I can change those unless you don't mind getting them as I am so afraid of messing something up after all your hard work on this.

thank you sooooo Much Leith! You're a Life Saver

So it works! And looks great. I am not sure why this version does not want to send if Outlook is closed. The one you did yesterday sent regardless. If we could get that straight and add the claim # and a short instruction note for me to be able to add a couple more email recipients in the future, I think we will have it. I will just need a clean sheet again, starting with Row 3. I did notice the notes in rows further down all of a sudden were in bold in column B where the Date and Time is. I can change those unless you don't mind getting them as I am so afraid of messing something up after all your hard work on this.

thank you sooooo Much Leith! You're a Life Saver

Ok, I just checked the Processes. Nothing with Outlook however, I sent you a conventional email to ensure my email is working and while I had Outlook literally open, full screen, I navigated to your second workbook to varify your email and when I went to close the workbook that time, the automatic sending email box popped up saying the email was being sent in my behalf. I allowed and quickly looked at Outlook and I saw it auto-send. Then I looked in sent items in Outlook and it now shows that an auto-email was sent to you. So,,,,, It must have something to do with Outlook not being completely open. If it is closed, it doesn't want to send. Interesting. Let me know if you get it now. Again, you should receive two emails. My conventional (manual) email and the auto-email. Maybe we're onto something.

thank you,

Hey Leith, were we going to be able to add the claim # to the worksheet and have it display in the subject line of the auto-emails?

Thank you,

Leith, are you able to assist me any further?


thanks

Nice! Is this not the hardest excel project you've taken on here in this thread?

thank you,

Got it, thank you. It is not working for some reason. Just closes like a normal spreadsheet. No auto-email like before. Hmmmm? I think it needs a little more tweaking.


thank you,