Results 1 to 6 of 6

Thread: VBA and Hidden Columns

  1. #1
    New Member Airwave's Avatar
    Join Date
    Aug 2019
    Location
    United Kingdom
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA and Hidden Columns

    I am trying to get the below to work in columns that are hidden from view in Excel. It does work, but only if the columns are not hidden. Is there a way around this?

    Code:
    With Sendrng
    .Parent.Select
    Set Rng = ActiveCell
    .Select
    ActiveWorkbook.EnvelopeVisible = True
    With .Parent.MailEnvelope
    For Each cell In Columns("P").Cells.SpecialCells(xlCellTypeVisible)
    If cell.Value Like "*@*" Then
    EmailAddr = EmailAddr & ";" & cell.Value
    End If
    Also, in the same workbook, I have a script that copies a selection into a new Outlook e-mail. I want the selection to open in a new Outlook mail window so the user can check it before pressing send. Someone suggested using .Display instead of .Send, but this does not work; it still goes straight to send.

    Code:
    With .Item
                
    .SentOnBehalfOfName = "emailaddresshere"
    .To = EmailAddr
    .CC = "emailaddresshere"
    .BCC = ""
    .Subject = "E-mail"
    .Send
                
    End With

  2. #2
    Board Regular
    Join Date
    Apr 2019
    Posts
    83
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA and Hidden Columns

    Code:
    With .Item
                
    .SentOnBehalfOfName = "emailaddresshere"
    .To = EmailAddr
    .CC = "emailaddresshere"
    .BCC = ""
    .Subject = "E-mail"
    .Display
                
    End With
    Edit: oops, I did not spot that you were trying .Display, weird, should work. Paste more code where email is created, maybe something is missing or in incorrect place.
    Last edited by KOKOSEK; Aug 20th, 2019 at 10:55 AM.

  3. #3
    New Member Airwave's Avatar
    Join Date
    Aug 2019
    Location
    United Kingdom
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA and Hidden Columns

    Quote Originally Posted by KOKOSEK View Post
    Code:
    With .Item
                
    .SentOnBehalfOfName = "emailaddresshere"
    .To = EmailAddr
    .CC = "emailaddresshere"
    .BCC = ""
    .Subject = "E-mail"
    .Display
                
    End With
    Edit: oops, I did not spot that you were trying .Display, weird, should work. Paste more code where email is created, maybe something is missing or in incorrect place.
    Thanks for the reply.

    I think this is the snippet where the e-mail is created;

    Code:
    Set Sendrng = Worksheets("Roadworks Notification Template").Range("B2:I19")
    
    
    Set AWorksheet = ActiveSheet
    
    
    With Sendrng
    .Parent.Select
    Set Rng = ActiveCell
    .Select
    ActiveWorkbook.EnvelopeVisible = True
    With .Parent.MailEnvelope
    On Error Resume Next
    For Each cell In Columns("P").Cells.SpecialCells(xlCellTypeVisible)
    If cell.Value Like "*@*" Then
    EmailAddr = EmailAddr & ";" & cell.Value
    End If
    Next
                
    With .Item
                
    .SentOnBehalfOfName = "emailaddresshere"
    .To = EmailAddr
    .CC = "emailaddresshere"
    .BCC = ""
    .Subject = "E-mail"
    .Send
                
    End With
    
    
    End With
    
    
    Rng.Select
    End With
    It I use .Display, it seems to open a mail in Excel itself, but there is no send button and it does not populate the recipients. If I use .Send, it goes straight to Outlook and sends it as it should. But, it would be nice if users could see the e-mail first to check the contents, and then click send.

  4. #4
    Board Regular
    Join Date
    Apr 2019
    Posts
    83
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA and Hidden Columns

    You would need to control Outlook directly instead of using the MailEnvelope method.
    Try this (of course need to be tweak a bit to your needs):

    Code:
    Function MailWithFile(FileName As String, StrTo As String, StrCC As String, StrSubject As String, StrBody As String, Send As Boolean)
        Dim OutApp As Object
        Dim OutMail As Object
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        On Error Resume Next
        With OutMail
            .To = StrTo
            .CC = StrCC
            .BCC = ""
            .Subject = StrSubject
            .HTMLBody = StrBody
            .Attachments.Add FileName
            If Send = True Then
                .Send
            Else
                .Display
            End If
        End With
        On Error GoTo 0
        Set OutMail = Nothing
        Set OutApp = Nothing
    End Function
    Last edited by KOKOSEK; Aug 21st, 2019 at 05:32 AM.

  5. #5
    New Member Airwave's Avatar
    Join Date
    Aug 2019
    Location
    United Kingdom
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA and Hidden Columns

    Quote Originally Posted by KOKOSEK View Post
    You would need to control Outlook directly instead of using the MailEnvelope method.
    Try this (of course need to be tweak a bit to your needs):

    Code:
    Function MailWithFile(FileName As String, StrTo As String, StrCC As String, StrSubject As String, StrBody As String, Send As Boolean)
        Dim OutApp As Object
        Dim OutMail As Object
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        On Error Resume Next
        With OutMail
            .To = StrTo
            .CC = StrCC
            .BCC = ""
            .Subject = StrSubject
            .HTMLBody = StrBody
            .Attachments.Add FileName
            If Send = True Then
                .Send
            Else
                .Display
            End If
        End With
        On Error GoTo 0
        Set OutMail = Nothing
        Set OutApp = Nothing
    End Function
    Thank you again for the reply.

    Unfortunately, I keep getting stuck in a "expected end sub" and "expected end with" loop of errors. This is the whole code;

    Code:
    Sub Send_Notification()
    
    
    If Range("C8").Value = "" Then
    MsgBox ("Date is a mandatory field"), vbCritical, "Notifcation Template"
    Range("C8").Select
    Exit Sub
    End If
    
    
    ActiveSheet.Unprotect Password:="kestrelhouse"
    Dim AWorksheet As Worksheet
    Dim Sendrng As Range
    Dim Rng As Range
        
    With Application
    .ScreenUpdating = False
    .EnableEvents = False
    End With
    
    
    Set Sendrng = Worksheets("Notification Template").Range("B2:I19")
    
    
    Set AWorksheet = ActiveSheet
    
    
    With Sendrng
    .Parent.Select
    Set Rng = ActiveCell
    .Select
    ActiveWorkbook.EnvelopeVisible = True
    With .Parent.MailEnvelope
    On Error Resume Next
    For Each cell In Columns("K").Cells.SpecialCells(xlCellTypeConstants)
    If cell.Value Like "*@*" Then
    EmailAddr = EmailAddr & ";" & cell.Value
    End If
    Next
                
    Function MailWithFile(FileName As String, StrTo As String, StrCC As String, StrSubject As String, StrBody As String, Send As Boolean)
    Dim OutApp As Object
    Dim OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail
    .To = EmailAddr
    .CC = StrCC
    .BCC = ""
    .Subject = "E-mail"
    .HTMLBody = StrBody
    .Attachments.Add FileName
    If Send = True Then
    .Send
    Else
    .Display
    End If
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
    End Function
    
    
    Rng.Select
    End With
    
    
    AWorksheet.Select
        
    Dim answer As Integer
    answer = MsgBox("Would you like to send another?", vbYesNo + vbQuestion)
    If answer = vbYes Then
    ActiveSheet.Protect Password:="kestrelhouse"
    Sheet1.Range("F8, I8, C10, F10, I10, C12, F12, I12, C14").ClearContents
    Sheet1.Box1.Value = False
    Sheet1.Box2.Value = False
    Else:  With Application
    .ScreenUpdating = True
    .EnableEvents = True
    End With
    ActiveWorkbook.EnvelopeVisible = False
    CloseBook2 = True
    ActiveWorkbook.Close savechanges:=False
    End If
    End Function

  6. #6
    Board Regular
    Join Date
    Apr 2019
    Posts
    83
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA and Hidden Columns

    as 1. Take function MailWithFile outside Send_Notification sub.
    as 2. use indents like below:

    Code:
         Set Rng = ActiveCell
        .Select
        ActiveWorkbook.EnvelopeVisible = True
        With .Parent.MailEnvelope                                   'with starts
            On Error Resume Next
            For Each cell In Columns("K").Cells.SpecialCells(xlCellTypeConstants)
                If cell.Value Like "*@*" Then                       'if starts
                    EmailAddr = EmailAddr & ";" & cell.Value
                End If                                                         'if stops
            Next
        End With                                                             'with stops
    it really helps to find out problems.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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