Results 1 to 2 of 2

Add Checkboxes that will return emails addresses in the BCC field into Outlook

Threaded View

  1. #1
    Registered User
    Join Date
    03-03-2017
    Location
    Grand Rapids, Michigan
    MS-Off Ver
    Microsoft Office 365 Business
    Posts
    3

    Add Checkboxes that will return emails addresses in the BCC field into Outlook

    I am building an email in Excel for Outlook using the following code:

        Sub Mail_Selection_Range_Outlook_Body()
        'Don't forget to copy the function RangetoHTML in the module.
        'Working in Excel 2000-2016
            Dim rng As Range
            Dim OutApp As Object
            Dim OutMail As Object
        
            Set rng = Nothing
            On Error Resume Next
            'Only the visible cells in the selection
            'Set rng = Selection.SpecialCells(xlCellTypeVisible)
            'You can also use a fixed range if you want
            Set rng = Sheets("Volume Template").Range("K4:L14").SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
        
            If rng Is Nothing Then
                MsgBox "The selection is not a range or the sheet is protected" & _
                       vbNewLine & "please correct and try again.", vbOKOnly
                Exit Sub
            End If
        
            With Application
                .EnableEvents = False
                .ScreenUpdating = False
            End With
        
            Set OutApp = CreateObject("Outlook.Application")
            Set OutMail = OutApp.CreateItem(0)
        
            On Error Resume Next
            With OutMail
                .To = ""
                .CC = ""
                .BCC =
                .Subject = "UTS VOLUME QUOTE REQUEST"
                .HTMLBody = RangetoHTML(rng)
                .Display   'or use .Send
            End With
            On Error GoTo 0
        
            With Application
                .EnableEvents = True
                .ScreenUpdating = True
            End With
        
            Set OutMail = Nothing
            Set OutApp = Nothing
        End Sub
        
        
        Function RangetoHTML(rng As Range)
            Dim fso As Object
            Dim ts As Object
            Dim TempFile As String
            Dim TempWB As Workbook
        
            TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
        
            'Copy the range and create a new workbook to past the data in
            rng.Copy
            Set TempWB = Workbooks.Add(1)
            With TempWB.Sheets(1)
                .Cells(1).PasteSpecial Paste:=8
                .Cells(1).PasteSpecial xlPasteValues, , False, False
                .Cells(1).PasteSpecial xlPasteFormats, , False, False
                .Cells(1).Select
                Application.CutCopyMode = False
                On Error Resume Next
                .DrawingObjects.Visible = True
                .DrawingObjects.Delete
                On Error GoTo 0
            End With
        
            'Publish the sheet to a htm file
            With TempWB.PublishObjects.Add( _
                 SourceType:=xlSourceRange, _
                 Filename:=TempFile, _
                 Sheet:=TempWB.Sheets(1).Name, _
                 Source:=TempWB.Sheets(1).UsedRange.Address, _
                 HtmlType:=xlHtmlStatic)
                .Publish (True)
            End With
        
            'Read all data from the htm file into RangetoHTML
            Set fso = CreateObject("Scripting.FileSystemObject")
            Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
            RangetoHTML = ts.readall
            ts.Close
            RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                                  "align=left x:publishsource=")
        
            'Close TempWB
            TempWB.Close savechanges:=False
        
            'Delete the htm file we used in this function
            Kill TempFile
        
            Set ts = Nothing
            Set fso = Nothing
            Set TempWB = Nothing
        End Function
    I want to use check boxes (using the form controls) to decide whether an email is used.

    If the check box reference cell is true input the value of a cell (an email address) into the BCC field.

    If Cell H4=True return Cell F4.

    Going down a list of about 30 entries so this needs to loop until there are no more check boxes

    How do I write this code?
    Last edited by AliGW; 03-04-2017 at 02:07 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Outlook 2010, send all emails from outlook outbox
    By joao1232 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-22-2016, 07:33 PM
  2. Track Outlook Emails - sent emails in Excel
    By baluraipur in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-06-2015, 09:18 AM
  3. Replies: 2
    Last Post: 01-27-2015, 12:10 PM
  4. [SOLVED] Sending Outlook emails from Excel; Limits to three emails only?
    By BPSJACK in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-29-2013, 06:53 AM
  5. Send outlook emails with message in cells to individual emails associated with them
    By abinayan in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-21-2011, 06:11 AM
  6. Send emails to multiple addresses
    By jenksie101 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-02-2010, 09:53 AM
  7. Sending Emails FROM Multiple Addresses
    By jamesryan in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-18-2009, 07:29 AM

Tags for this Thread

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