VBA and Hidden Columns

[Airwave]

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?

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.

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

 

[KOKOSEK]

With .Item
            
.SentOnBehalfOfName = "emailaddresshere"
.To = EmailAddr
.CC = "emailaddresshere"
.BCC = ""
.Subject = "E-mail"
[B].Display[/B]
            
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.

 

[Airwave]

With .Item
            
.SentOnBehalfOfName = "emailaddresshere"
.To = EmailAddr
.CC = "emailaddresshere"
.BCC = ""
.Subject = "E-mail"
[B].Display[/B]
            
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;

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.

 

[KOKOSEK]

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):

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

 

[Airwave]

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):

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;

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

 

[KOKOSEK]

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

     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.