Confirmation of Coding requiring help

December 14, 2016 at 10:40:37
Specs: Windows 64
Sub PrintAnalys2222()

    Application.ScreenUpdating = False
        
    If MsgBox("YOU ARE ABOUT TO E-MAIL THE ANALYSIS SHEETS. HAVE YOU SELECTED THE CORRECT COURSE? ARE YOU SURE?", vbYesNo) = vbYes Then
    
' Declare variables.
    Dim MyRange As Range
    Dim MyCell As Range
' Define the target Range.
    Set MyRange = Sheets("GRPLIST").Range("K2:K49")
' Start looping through the range.
    For Each MyCell In MyRange
' Do something with each cell.
    If MyCell.Value = "" Then
        MsgBox ("ALL E-MAILS HAVE BEEN SENT")
        
        Sheets("GRPLIST").Select
     Exit Sub
  
     Else
    
' apply name to
    Sheets("ANALYS2").Range("H4") = MyCell.Value

    End If
    
    Sheets("ANALYS2").Visible = True
    Sheets("ANALYS2").Select
        Sheets("ANALYS2").Unprotect Password:="2L4NTJEzNuKn"
    Sheets("ANALYS2").Cells.Select
        Selection.EntireRow.Hidden = False

    Dim cell As Range
    For Each cell In Sheets("ANALYS2").Range("A9:A130")
    If (cell.Value) = "0" Then
    cell.EntireRow.Hidden = True
    End If
    Next
    
    Sheets("ANALYS2").Range("A2").Select
    
    Sheets("LEGEND").Protect Password:="2L4NTJEzNuKn", DrawingObjects:=True, Contents:=True, Scenarios:=True
    
' prints individual analysis sheets

    Dim OutApp As Object
    Dim OutMail As Object

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With


    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
        "C:\Users\tonyg_000\Desktop\SAS workbooks\SASnew windows10 2.3.6.pdf", Quality _
        :=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
          
        On Error Resume Next
        With OutMail
            .to = "tonygibbxx@xxxxxxx.com"
            .CC = ""
            .BCC = ""
            .Subject = "YOUR SCORECARD ANALYSIS FOR THE YEAR SO FAR"
            .Body = "Hi there, I hope you find this of interest"
            .Attachments.Add "C:\Users\tonyg_000\Desktop\SAS workbooks\SASnew windows10 2.3.6.pdf"
            'You can add other files also like this
            '.Attachments.Add ("C:\test.txt")
            .Send   'or use .Display
        End With
        On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    
    Sheets("ANALYS2").Select
    ActiveWindow.SelectedSheets.Visible = False
               
' Get the next cell in the range
    Next MyCell
    
    End If
    
    Sheets("GRPLIST").Select
               
    Application.ScreenUpdating = True
End Sub

Hi Guys,
I have three questions relating to the bit of code above
Firstly, as the code is, it works like a dream and I can send an email with the click of a forms button.

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
        "C:\Users\tonyg_000\Desktop\SAS workbooks\SASnew windows10 2.3.6.pdf", Quality _
        :=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

This part of the code, which exports the Worksheet as a PDF, I would like to change the file name to then title the file as

Sheets("ANALYS2").Range("H4")
, this being a Name. Not sure what alterations I need to do

Next
As the code loops through the Range, ("K2:K49"), I need to determine if there is an e-mail address associated to the Cell. Lets say that ("K2") is = to "Tony Gibb". I need to then see if the E-mail address is in ("J2") - ("J2:J49") is a range of corresponding e-mail addresses.
If the e-mail address is there, I then need to refer to the e-mail address in the following line of code

With OutMail
            .to = "tonygibbxx@xxxxxxx.com"
Not sure how the coding should appear

If an e-mail address is available, the code continues to

' Get the next cell in the range
    Next MyCell

If there is no e-mail address, I then need to start the loop again going through the process until the first blank cell in Range("K2:K49")

Need some direction on this
Regards
Tony


See More: Confirmation of Coding requiring help

Report •

#1
December 14, 2016 at 11:29:56
Assuming that only the file name is in the cell, not the file path or extension, try this (untested) solution. Basically you need to build the filename via a concatenation of hard coded text strings and cell references. It all depends how much of the full file path/file name string is in the cell.

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
        "C:\Users\tonyg_000\Desktop\SAS workbooks\" & _
         Sheets("ANALYS2").Range("H4") & _
         ".pdf",

As far as the email address question, why not check for an @ in the cells that might/should contain an email address? The presence of @ doesn't guarantee an email address, but the lack of one sure guarantees that there isn't an email address in the cell.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#2
December 14, 2016 at 14:04:42
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
        "C:\Users\tonyg_000\Desktop\SAS workbooks\" & Sheets("ANALYS2").Range("H4") & ".pdf", Quality _
        :=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
        
     Dim EmailAddress As String
     
        EmailAddress = VLookup(Sheets("ANALYS2").Range("H4"), Sheets("LEGEND").Range("AP2:AT49"),  5,  False)
          
        On Error Resume Next
        With OutMail
            .to = " & EmailAddress & "

Hi DD03
Been trying to work this coding and thought about using the VLOOKUP to ascertain the email address. The following line of code from the above

EmailAddress = VLookup(Sheets("ANALYS2").Range("H4"), Sheets("LEGEND").Range("AP2:AT49"),  5,  False)

Errors straight away. What do I need to define the function?


Report •

#3
December 14, 2016 at 18:27:47
As much as I like to help, it seems a shame that you have to wait hours for someone in this forum to respond. I'll let you in on a little secret. I don't know all the answers, but I know how to use Google.

Have you done a Google search for how to use VLOOKUP in VBA?

I think you'll find your answer in a matter of seconds.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
December 15, 2016 at 14:40:04
Hi DD03,
As per your suggestion, I tend to look online before posting, and only really come to the Forum when I get confused within myself. Finally tracked something down that actually works, as per below

Dim OutlookApp As Object
    Dim Mess As Object, Recip As String
    Recip = Sheets("ANALYS2").Range("AD2").Value
    Set OutlookApp = CreateObject("Outlook.Application")
    Set Mess = OutlookApp.CreateItem(olMailItem)
    With Mess
        .Subject = "SCORECARD ANALYIS FOR THE YEAR "
        .Body = "Hi, I hope you find the attached of interest, please let me know that you have recieved this  8"
        .Attachments.Add "C:\Users\tonyg_000\Desktop\SAS emails\" & Sheets("ANALYS2").Range("H4") & ".pdf"
        .Recipients.Add Recip
        .Send
    End With

Would like to add that I do appreciate the assistance you have and no doubt will continue to give, as with some of the others guys, Mike M to name one

regards
Tony


Report •

#5
December 15, 2016 at 14:49:04
I'm confused. You asked for help with a VLOOKUP instruction within VBA.

A Google search of something like "using vlookup in vba" would have shown that all you needed to do was add Application. in front of Vlookup, since Vlookup is not native to VBA.

Instead, you came back back with something totally different. Why did you abandon Vlookup?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
December 15, 2016 at 15:12:23
Exactly what I proceeded to do, but must have misunderstood the explanation.
I proceed to put Application.WorksheetFunction and also tried Application.WorkBookFunction but neither worked.
Then decided to try another approach, whereby the Data in Analys2!AD2 is a VLOOKUP in relation to Analys2!H4. Got exactly what was required.
As for you not knowing all the answers, your knowledge of Excel and VBA is exceptional and I for one are glad that you react on the Forum

Report •

Ask Question