+ Reply to Thread
Results 1 to 2 of 2

Only want button to send email when its a certain colour

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    04-09-2014
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    250

    Only want button to send email when its a certain colour

    e.g. I can normally achieve this. with the below code:

    'Rectangle 40 - Auto email & change shape fill
    
    Sub SendMailAndChangeImageR40()
    
       Dim outapp As Object
       Dim outmail As Object
    On Error Resume Next
    Err.Clear
    Set outapp = GetObject(class:="outlook.application")
    If Err <> 0 Then Set outapp = CreateObject("outlook.application")
    outapp.session.logon
    Set outmail = outapp.createitem(0)
    
    On Error Resume Next
    If ActiveSheet.Shapes("rectangle 40").Fill.ForeColor.RGB = RGB(255, 255, 255) Then
    With outmail
    .To = ""
    .CC = ""
    .BCC = ""
    .Subject = ""
    .Body = ""
    .Send
    End With
    
    Set outapp = Nothing
    Set outmail = Nothing
    
    End If
    
    End Sub
    but this doesn't seem to work anymore when I include more code. I also need the shape to change from displaying an image - to going back to a white fill.


    the code I use for that, is this:

    sub changefill()
    
    Dim shp As Shape
    Set shp = ActiveSheet.Shapes("rectangle 40")
    With shp.Fill
    .Visible = msoTrue
    If .Type = msoFillPicture Then
    .Solid
    Else
    .UserPicture "C:\Users\boymam\Desktop\Graphics\tick.jpg"
    End If
    End With
    
    end sub

    but when I ADD that into the top OR bottom of my email code. to blend as one.. it sends the email even when the fill has changed to the image.

    I want the email to ONLY send when the shape is WHITE. when the shape is my image - I don't want it to send when I click etc.

  2. #2
    Forum Contributor
    Join Date
    03-12-2013
    Location
    Chicago, USA
    MS-Off Ver
    Excel 2007
    Posts
    230

    Re: Only want button to send email when its a certain colour

    I'm not very familiar with colors, but I have two suggestions.

    In the IF statement where you change the picture to the .jpg picture, include another line in the ELSE section that does one of the following:
    1) Exits the sub
    2) Or if you want the sub procedure to continue running, declare a boolean variable and set it to TRUE when you set the shape to your .jpg picture. Then, when you enter the IF statement to send the email, check to see if the variable is TRUE. If it is, then ignore the send email code.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. auto send email with cell background colour
    By vipulhumein in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-09-2013, 03:25 PM
  2. Excel to send email from VBA button
    By Benjamin2008 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-08-2012, 07:56 PM
  3. Button - Send email
    By Wiets in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-06-2008, 09:04 PM
  4. command button to send email
    By artromanov in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-09-2007, 09:02 PM
  5. [SOLVED] send email from button
    By andreww in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-20-2005, 11:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1