email from Excel after workbook is saved using Gmail

[niklas24]

I'm trying to have an email auto generate after a workbook is saved. I don't want to send the workbook in the email, just an email notification to a list of people to say that the spreadsheet has a new entry so they actually have to open it and respond (if I could put a link to the location of the workbook that would work). Also the spreadsheet is "shared" so multiple people can edit it at once, so I don't think it will remain as "shared" and continue to update if it is downloaded from an email. About 25 people have access to this spreadsheet and anyone can enter/edit an entry...so I'm trying have an email sent if anyone saves the workbook. There are 2 sheets in the workbook that I want this functionality to work with.

My agency uses Gmail but our email addresses do not have <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">@gmail.com</code> in them. Instead we are using our <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">.gov</code> email addresses through gmail somehow. I'm not sure if this is even relevant but thought I'd mention it. I've searched several online forums, but can't seem to find anything.

Does anyone know of any code to do this?
I'm a novice with VBA and I got the email part to work but I want it to email when the workbook is saved. This is the code I am currently using which does email when I manually run the code, but not upon saving it:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)


End Sub


Sub CDO_Mail_Small_Text()
Dim iMsg As Object
Dim iConf As Object
Dim strbody As String
'    Dim Flds As Variant


Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")


    iConf.Load -1    ' CDO Source Defaults
     Set Flds = iConf.Fields
     With Flds
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "xxx@xxx.gov"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "xxxxxxxxx"
     End With


strbody = "Hi there" & vbNewLine & vbNewLine & _
          "The tech team spreadsheet has been updated.  Please open it and review the changes."


With iMsg
    Set .Configuration = iConf
    .To = "xxx@xxx.gov"
    .CC = ""
    .BCC = ""
    .From = """name"" <xxx@xxx.com>"
    .Subject = "test"
    .TextBody = strbody
    .Send
End With


End Sub

I have all of the code under "This Workbook" and it compiles without any errors although when I run it it doesn't send an email. Should either of these be in a module?? What am I doing wrong?

Thanks in advance.</xxx@xxx.com>