Previous Topic Next Topic

Auto Email when cells in range are edited

Closed
Iconyx - May 23, 2010 at 09:26 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 25, 2010 at 06:05 PM
Hello,

Heres my problem:

The company I work for currently uses an excel spreadsheet for scheduling. Its annoying and cumbersome. Once updated, its saved and its able to be pulled from the in office intranet. If I want to check it I have to remote in from home to my office PC and pull up the intranet page, it takes way too long. Since they don't want to make a transition to actual scheduling software...I want to automate an email process in the spreadsheet so that every change made to a persons schedule, automatically emails them the details of the change.

The sheet is setup with dates at the top (row1) and names of techs on the left (column A).
The cells to the right of the techs name are filled in with assignments for each day (1 cell=1day). What I'm looking to do is write some script or macro (NOTE: I am not at all VBScript savvy) that emails a tech if a cell in his row is edited/updated. So that I wont get an email from another techs row or vice versa.

So far, Ive been able to make a button that sends an email to me with the contents of my row, but I cant make multiple buttons with the same Sub name apparently (says something about ambiguous name) and I want to automate the process anyway. The code I have is as follows:


Sub Send_Range()

' Select the range of cells on the active worksheet.
ActiveSheet.Range("B1:F1", "B2:E2").Select

' Show the envelope on the ActiveWorkbook.
ActiveWorkbook.EnvelopeVisible = True

' Set the optional introduction field thats adds
' some header text to the email body. It also sets
' the To and Subject lines. Finally the message
' is sent.
With ActiveSheet.MailEnvelope
.Introduction = "This is a copy of your current schedule"
.Item.To = "name@email.com"
.Item.Subject = "Schedule Change"
.Item.Send
End With
End Sub


Side note: Anyone happen to know why I can view the contents of the cells when I open the email I described in the paragraph directly above just fine, but when opened in my blackberry I only get the "This is a copy of your current schedule" line and nothing else. Any help would be greatly appreciated.


Related:

1 response

Answer 1 / 1
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 25, 2010 at 06:05 PM
On a very highlevel. the reason is that you cannot have two similar functions. If range is different you can call the function with parameter. Some thing like 


Sub Send_Range(myRange1 as String, myRange2 as String)

    ' Select the range of cells on the active worksheet.
    ActiveSheet.Range(myRange1 , myRange2 ).Select
    
    ' Show the envelope on the ActiveWorkbook.
    ActiveWorkbook.EnvelopeVisible = True
    
    ' Set the optional introduction field thats adds
    ' some header text to the email body. It also sets
    ' the To and Subject lines. Finally the message
    ' is sent.
    With ActiveSheet.MailEnvelope
        .Introduction = "This is a copy of your current schedule"
        .Item.To = "name@email.com"
        .Item.Subject = "Schedule Change"
        .Item.Send
    End With
End Sub




If this does not answer, could you please upload a sample file with sample data etc on some shared site like https://authentification.site , http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee.
0