Solved Data Validation List automatically updates

September 27, 2018 at 09:24:30
Specs: Windows 64
Hi Guys,
Just a little poser that I haven't been able to find an answer to.
D30 - D36 are a list of DATA Validated Cells, They select from another list of PDF Files in cells P25 -P31
Not all PDF files will be selected at anyone time so this allows me to select the ones that I wish to email

HOWEVER, lets use P30 as the example. This is a standard PDF file name in my C: drive. I have been able to get the file name, C:\Users\Desktop\SAS main\SAS emails\"another".pdf to change the name when performing a loop, the name corresponds to the email address
When I perform the loop the DATA validated cell does not change accordingly

Is there a way that , no matter which data validated cell is used (D30 - D36) that the info changes automatically to what ever the file address is in P30
Looking forward to some assistance on this

Regards
Tony

message edited by tonygibb


See More: Data Validation List automatically updates

Reply ↓  Report •

✔ Best Answer
October 20, 2018 at 08:45:36
Try the following. It seems like VBA doesn't like it when a cell reference is used in the Attachments.Add instruction. I think it's because it is not seeing the contents as a text string. Using a variable to hold the file path, like you did in your code, seems to work.

I tried this with 3 files from 3 different file locations and it attached all three.

 'Add attachments from D26:D32 Based On numAttach value
          If numAttach > 0 Then
            For attachCell = 26 To 26 + numAttach - 1
               nxtAttach = Sheets("EBODY").Range("D" & attachCell)
               .Attachments.Add nxtAttach
            Next
          End If

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

message edited by DerbyDad03



#1
September 27, 2018 at 10:24:45
I do not understand what you mean by this:

"I have been able to get the file name, C:\Users\Desktop\SAS main\SAS emails\"another".pdf to change the name when performing a loop, the name corresponds to the email address"

You are able "to get the file name ... to change the name..."

What does that mean?

"...the name corresponds to the email address"

What "name" are you referring to? What email address?

"When I perform the loop the DATA validated cell does not change accordingly"

What "loop" are you referring to? Is there a macro involved here?

There is so much that I can't figure out from your post.

message edited by DerbyDad03


Reply ↓  Report •

#2
September 27, 2018 at 12:09:57
Hi DD03, been awhile since my last post
I have 26 email addresses that correspond to individual pdf files
Cell P30 reads as the pdf file address, C:\Users\Desktop\SAS main\SAS emails\"another".pdf
"another" is the name of the addressee, so could read C:\Users\Desktop\SAS main\SAS emails\Tony Gibb.pdf. By running a loop in VBA, I can go through the list of email addresses and find the corresponding pdf file. This bit is working fine
I am running a macro that will send out an email to each address
However, I have what I will refer to as up to 7 attachments, not all attachments are required every time
It could be attachment 1,3,4 and 7, or a variant
Hence I have 7 data validation lists in cells D30 - D36
What I would Like to happen is that the pdf address in cell P30 changes in which ever cell , between D30-D36 (Validation List) that P30 appears in
At the moment , if the data changes in P30, I would have to manually go into the Data Validation list in say D32 to change it to what is required
I am trying to automate the change in P30 to which ever cell D30-D36 is being used

In essence, P30 data changes via a macro
That change needs to automatically alter in the Validation list

If I can find the answer to the auto change, I can incorporate this in a very large macro I have that sends out the emails

Tony

message edited by tonygibb


Reply ↓  Report •

#3
September 27, 2018 at 16:24:42
Without seeing your workbook or code, it's kind of hard to offer a specific solution. All I can do is perhaps ask a question that might give me a better understanding.

What can't you write a search routine using the Find and perhaps FindNext methods to search for the partial string and change it?

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


Reply ↓  Report •

Related Solutions

#4
September 28, 2018 at 11:29:00
Hi DD03, sorry not got back quicker, hard day at the office
Let me try and explain this a different way

D30 is a Data Validated List cell. The list is from P25 - P31
Lets say that I change the text in P26. I would then have to click on the Validated list for the change to take place within the list
What I am trying to establish is whether the change can automatically happen to the Validated list without clicking on the cell for the change to take effect

Tony


Reply ↓  Report •

#5
September 28, 2018 at 12:41:33
I must be thick 'cuz I still don't get it. What's the point of a putting data validation list in a cell if you don't want to click on the cell to access the list? If you are accessing the list data via VBA, why not just access the original data that is used to build the list?

In any case, what about this? This code will "rebuild" the data validation list right after P30 is changed. That appears to address this requirement:

"What I am trying to establish is whether the change can automatically happen to the Validated list without clicking on the cell for the change to take effect"

If this isn't what you are looking for, please explain why this doesn't fulfill the requirement so I can (maybe) understand what I am missing.

Sub UpdateValidationList()

'Change value in P30
  Range("P30") = "My New Entry"

'Build array from range
Dim myList(7) As String
  myList(1) = Range("P25")
  myList(2) = Range("P26")
  myList(3) = Range("P27")
  myList(4) = Range("P28")
  myList(5) = Range("P29")
  myList(6) = Range("P30")
  myList(7) = Range("P31")

'Create Validation List from array

    With Range("D30").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
         Operator:=xlBetween, Formula1:=Join(myList, ",")
    End With

End Sub

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


Reply ↓  Report •

#6
September 28, 2018 at 23:32:15
Hi DD03
That code is absolutely fine. However, I then still have to click on D30 to select the text that is now P30

The change occurs in mylist(7) but does not automatically change in D30

1. D30 = Bill& Ben

2. P30 = Bill& Ben Run code and change P30 to Andy Pandy

3. D30 still = Bill& Ben

4. Manually click on D30, Bill& Ben are no longer on mylist(7), but Andy Pandy is

Lines 3 and 4 of the above is what I would like to do automatically
Is it possible or is the way that the selection process of a Data validation list works can only be done by manual process

Tony

Select Andy Pandy equals D30 = Andy Pandy


Reply ↓  Report •

#7
September 29, 2018 at 07:54:58
So, in other words, you aren't asking for a way to automatically update the data validation list when P30 is changed, you want to automatically update the data validation cell when P30 is changed.

You kept saying things like:

"That change needs to automatically alter in the Validation list"
"is whether the change can automatically happen to the Validated list"

We know that the list is being updated because the new value is available. That is why I have been so confused all along.

If you want D30 to show what is in P30 after the code has changed P30, simply set D30 equal to P30 within the code. An instruction as simple as this should work:

Range("D30") = Range("P30")

Note: The P30 value isn't being allowed in D30 because it is now in the updated source list, it is being allowed because VBA will essentially ignore the validation source list. i.e. This instruction will place Andy Pandy in the data validation cell even if Andy Pandy is not in the source list. A user can't manually enter Andy Pandy in the data validation cell if it was not in the source list (unless .ShowError = False) but VBA can.

Range("D30") = "Andy Pandy"

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


Reply ↓  Report •

#8
September 30, 2018 at 11:40:43
Hi DD03
New it would be something simple, think I've been over thinking this
I will give this a try and come back to you
Many thanks for your help
Tony

Reply ↓  Report •

#9
September 30, 2018 at 12:33:00
DD03, works fine, again thankyou

Reply ↓  Report •

#10
September 30, 2018 at 12:58:56
I'm still confused as to why you are using a drop down list when you are setting the value via VBA.

Unless of course you are also using the drop down as part of some manual process.

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


Reply ↓  Report •

#11
October 3, 2018 at 12:41:53
Hi DD03
Yes, you are correct, I have 7 dropdown boxes. Each dropdown box has the same list, 7 items
This allows me to send only one item or 7 , or 3 if I need to, and so on , allows a variant to what I email

I have attached the VBA so you can see what i was trying to achieve

Note that if , say dropdown box 3 was blank, then the vba would only add 2
attachments, and ignore ant attachments they may be present in dropbox 4 - 7


Sub Emailplayer()

    Application.ScreenUpdating = False
        
    If MsgBox("YOU ARE ABOUT TO SEND A GENERAL-MAIL", vbYesNo) = vbYes Then
    
    If MsgBox("YOU WILL NEED TO OPEN 'OUTLOOK'. THE E-MAILS WILL NOT BE SENT UNTIL 'OUTLOOK' IS OPENED. YOU CAN OPEN 'OUTLOOK' AT A LATER STAGE FOR THE E-MAILS TO SEND. DO YOU WISH TO CONTINUE?", vbYesNo) = vbYes Then
    
' Declare variables.
    Dim MyRange As Range
    Dim Mycell As Range
' Define the target Range.
    Set MyRange = Sheets("EBODY").Range("D36:D83")
' 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("EBODY").Select
     Exit Sub
  
     Else
         
     End If

' For each Mycell value, create pdf address in EBODY Range.P31
    Sheets("EBODY").Range("P31").Value = Application.WorksheetFunction.VLookup(Mycell, Sheets("EBODY").Range("D36:F83"), 3, True)
    Range("D30") = Range("P31")

    Dim OutlookApp As Object
    Dim Mess As Object, Recip As String
    Recip = Mycell
    
    Dim message As String
    
    Subj = Sheets("EBODY").Range("D3")
    Emess = Sheets("EBODY").Range("D5").Value & "
" & _
            Sheets("EBODY").Range("D6").Value & "
" & _
            Sheets("EBODY").Range("D7").Value & "
" & _
            Sheets("EBODY").Range("D8").Value & "
" & _
            Sheets("EBODY").Range("D9").Value & "
" & _
            Sheets("EBODY").Range("D10").Value & "
" & _
            Sheets("EBODY").Range("D11").Value & "
" & _
            Sheets("EBODY").Range("D12").Value & "
" & _
            Sheets("EBODY").Range("D13").Value & "
" & _
            Sheets("EBODY").Range("D14").Value & "
" & _
            Sheets("EBODY").Range("D15").Value & "
" & _
            Sheets("EBODY").Range("D16").Value & "
" & _
            Sheets("EBODY").Range("D17").Value & "
" & _
            Sheets("EBODY").Range("D18").Value & "
" & _
            Sheets("EBODY").Range("D19").Value & "
" & _
            Sheets("EBODY").Range("D20").Value & "
" & _
            Sheets("EBODY").Range("D21").Value & "
" & _
            Sheets("EBODY").Range("D22").Value & "


"
            
    Attach1 = Sheets("EBODY").Range("D26")
    attach2 = Sheets("EBODY").Range("D27")
    attach3 = Sheets("EBODY").Range("D28")
    attach4 = Sheets("EBODY").Range("D29")
    attach5 = Sheets("EBODY").Range("D30")
    attach6 = Sheets("EBODY").Range("D31")
    attach7 = Sheets("EBODY").Range("D32")
    
    If Attach1 = "" Then
    
    Set OutlookApp = CreateObject("Outlook.Application")
    Set Mess = OutlookApp.CreateItem(olMailItem)
    With Mess
        .Subject = Subj
        .HTMLBody = Emess
        .Recipients.Add Recip
        .Send
    End With
    
    Else
    
    If attach2 = "" Then
    
    Set OutlookApp = CreateObject("Outlook.Application")
    Set Mess = OutlookApp.CreateItem(olMailItem)
    With Mess
        .Subject = Subj
        .HTMLBody = Emess
        .Attachments.Add (Attach1)
        .Recipients.Add Recip
        .Send
    End With
    
    Else
    
    If attach3 = "" Then
    
    Set OutlookApp = CreateObject("Outlook.Application")
    Set Mess = OutlookApp.CreateItem(olMailItem)
    With Mess
        .Subject = Subj
        .HTMLBody = Emess
        .Attachments.Add (Attach1)
        .Attachments.Add (attach2)
        .Recipients.Add Recip
        .Send
    End With
        
    Else
    
    If attach4 = "" Then
    
    Set OutlookApp = CreateObject("Outlook.Application")
    Set Mess = OutlookApp.CreateItem(olMailItem)
    With Mess
        .Subject = Subj
        .HTMLBody = Emess
        .Attachments.Add (Attach1)
        .Attachments.Add (attach2)
        .Attachments.Add (attach3)
        .Recipients.Add Recip
        .Send
    End With
    
    Else
    
    If attach5 = "" Then
    
    Set OutlookApp = CreateObject("Outlook.Application")
    Set Mess = OutlookApp.CreateItem(olMailItem)
    With Mess
        .Subject = Subj
        .HTMLBody = Emess
        .Attachments.Add (Attach1)
        .Attachments.Add (attach2)
        .Attachments.Add (attach3)
        .Attachments.Add (attach4)
        .Recipients.Add Recip
        .Send
    End With
    
    Else
    
    If attach6 = "" Then
    
    Set OutlookApp = CreateObject("Outlook.Application")
    Set Mess = OutlookApp.CreateItem(olMailItem)
    With Mess
        .Subject = Subj
        .HTMLBody = Emess
        .Attachments.Add (Attach1)
        .Attachments.Add (attach2)
        .Attachments.Add (attach3)
        .Attachments.Add (attach4)
        .Attachments.Add (attach5)
        .Recipients.Add Recip
        .Send
    End With
    
    Else
    
    If attach7 = "" Then
    
    Set OutlookApp = CreateObject("Outlook.Application")
    Set Mess = OutlookApp.CreateItem(olMailItem)
    With Mess
        .Subject = Subj
        .HTMLBody = Emess
        .Attachments.Add (Attach1)
        .Attachments.Add (attach2)
        .Attachments.Add (attach3)
        .Attachments.Add (attach4)
        .Attachments.Add (attach5)
        .Attachments.Add (attach6)
        .Recipients.Add Recip
        .Send
    End With
    
    Else
    
    Set OutlookApp = CreateObject("Outlook.Application")
    Set Mess = OutlookApp.CreateItem(olMailItem)
    With Mess
        .Subject = Subj
        .HTMLBody = Emess
        .Attachments.Add (Attach1)
        .Attachments.Add (attach2)
        .Attachments.Add (attach3)
        .Attachments.Add (attach4)
        .Attachments.Add (attach5)
        .Attachments.Add (attach6)
        .Attachments.Add (attach7)
        .Recipients.Add Recip
        .Send
    End With
    
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    
' Get the next cell in the range
    Next Mycell
        
    End If
    End If
    
    
End Sub


Reply ↓  Report •

#12
October 3, 2018 at 15:22:09
OK, so if I understand you correctly, you manually choose items from the drop boxes in D26:D32 and then run the code. The code adds attachments from those cells until it sees a blank "choice" anywhere in D26:D32.

OK, if that's correct, I think you are making VBA work way too hard. Wouldn't this code do the same thing?

(I tested it with MsgBox's instead of creating emails and it seemed to work, so the code below has not been tested.)

Sub ShortOptionForAttaching()

'Determine Number of attachments - Count Cells in D26:D30 with data
 numAttach = 0
  For chkCell = 26 To 32
    If Sheets("EBODY").Range("D" & chkCell) <> "" Then
      numAttach = numAttach + 1
    Else: Exit For
    End If 
  Next
  
'Create email
  Set OutlookApp = CreateObject("Outlook.Application")
    Set Mess = OutlookApp.CreateItem(olMailItem)
    With Mess
        .Subject = Subj
        .HTMLBody = Emess
 
 'Add attachments from D26:D32 Based On numAttach value
          If numAttach > 0 Then
            For attachCell = 26 To 26 + numAttach - 1
             .Attachments.Add (Sheets("EBODY").Range("D" & attachCell))
            Next
          End If
        .Recipients.Add Recip
        .Send
    End With
End Sub

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

message edited by DerbyDad03


Reply ↓  Report •

#13
October 11, 2018 at 05:53:58
Hi DD03,
Thanks for coming back. I have tried with your untested 'Sub ShortOptionForAttaching()'

Not having much luck with it but will keep trying to utilise the coding

The attached is what I have manifested and processes how I envisaged, theres probably a lot simpler coding to get to where I got, but I do appreciate the help you have been giving


Sub Emailplayer22()

    Application.ScreenUpdating = False
        
    If MsgBox("YOU ARE ABOUT TO SEND A GENERAL-MAIL", vbYesNo) = vbYes Then
    
    If MsgBox("YOU WILL NEED TO OPEN 'OUTLOOK'. THE E-MAILS WILL NOT BE SENT UNTIL 'OUTLOOK' IS OPENED. YOU CAN OPEN 'OUTLOOK' AT A LATER STAGE FOR THE E-MAILS TO SEND. DO YOU WISH TO CONTINUE?", vbYesNo) = vbYes Then
    
     Sheets("EBODY").Unprotect Password:="2L4NTJEzNuKn"
     
     
     
' Declare variables.
    Dim MyRange As Range
    Dim Mycell As Range
' Define the target Range.
    Set MyRange = Sheets("EBODY").Range("D36:D83")
' 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("EBODY").Protect Password:="2L4NTJEzNuKn", DrawingObjects:=True, Contents:=True, Scenarios:=True
    
               
        Sheets("EBODY").Select
     Exit Sub
  
     Else
         
     End If
     
     
' For each Mycell value, create pdf address in EBODY Range.Q31
    Sheets("EBODY").Range("Q31").Value = Application.WorksheetFunction.VLookup(Mycell, Sheets("EBODY").Range("D36:F83"), 3, True)
 

' Confirm entry in D26  (((InStr(cell.Value, "Word1") > 0
    If InStr(Range("D26"), "SCORECARD ANALYSIS") > 0 Then
        Range("D26") = Range("Q31")
    Else
' Confirm entry in D27
    If InStr(Range("D27"), "SCORECARD ANALYSIS") > 0 Then
        Range("D27") = Range("Q31")
    Else
' Confirm entry in D28
    If InStr(Range("D28"), "SCORECARD ANALYSIS") > 0 Then
        Range("D28") = Range("Q31")
    Else
' Confirm entry in D29
    If InStr(Range("D29"), "SCORECARD ANALYSIS") > 0 Then
        Range("D29") = Range("Q31")
    Else
' Confirm entry in D30
    If InStr(Range("D30"), "SCORECARD ANALYSIS") > 0 Then
        Range("D30") = Range("Q31")
    Else
' Confirm entry in D31
    If InStr(Range("D31"), "SCORECARD ANALYSIS") > 0 Then
        Range("D31") = Range("Q31")
    Else
' Confirm entry in D32
    If InStr(Range("D32"), "SCORECARD ANALYSIS") > 0 Then
        Range("D32") = Range("Q31")
    Else
' Confirm entry in D33
    If InStr(Range("D33"), "SCORECARD ANALYSIS") > 0 Then
        Range("D33") = Range("Q31")
    Else
' Confirm entry in D34
    If InStr(Range("D34"), "SCORECARD ANALYSIS") > 0 Then
        Range("D34") = Range("Q31")
        
    Else
    
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    

    
' Create email and send

    Dim OutlookApp As Object
    Dim Mess As Object, Recip As String
    Recip = Mycell
    
    Dim message As String
    
    Subj = Sheets("EBODY").Range("D3")
    Emess = Sheets("EBODY").Range("D5").Value & "
" & _
            Sheets("EBODY").Range("D6").Value & "
" & _
            Sheets("EBODY").Range("D7").Value & "
" & _
            Sheets("EBODY").Range("D8").Value & "
" & _
            Sheets("EBODY").Range("D9").Value & "
" & _
            Sheets("EBODY").Range("D10").Value & "
" & _
            Sheets("EBODY").Range("D11").Value & "
" & _
            Sheets("EBODY").Range("D12").Value & "
" & _
            Sheets("EBODY").Range("D13").Value & "
" & _
            Sheets("EBODY").Range("D14").Value & "
" & _
            Sheets("EBODY").Range("D15").Value & "
" & _
            Sheets("EBODY").Range("D16").Value & "
" & _
            Sheets("EBODY").Range("D17").Value & "
" & _
            Sheets("EBODY").Range("D18").Value & "
" & _
            Sheets("EBODY").Range("D19").Value & "
" & _
            Sheets("EBODY").Range("D20").Value & "
" & _
            Sheets("EBODY").Range("D21").Value & "
" & _
            Sheets("EBODY").Range("D22").Value & "


"
            
    Attach1 = Sheets("EBODY").Range("D26")
    attach2 = Sheets("EBODY").Range("D27")
    attach3 = Sheets("EBODY").Range("D28")
    attach4 = Sheets("EBODY").Range("D29")
    attach5 = Sheets("EBODY").Range("D30")
    attach6 = Sheets("EBODY").Range("D31")
    attach7 = Sheets("EBODY").Range("D32")
    
    If Attach1 = "" Then
    
    Set OutlookApp = CreateObject("Outlook.Application")
    Set Mess = OutlookApp.CreateItem(olMailItem)
    With Mess
        .Subject = Subj
        .HTMLBody = Emess
        .Recipients.Add Recip
        .Send
    End With
    
    Else
    
    If attach2 = "" Then
    
    Set OutlookApp = CreateObject("Outlook.Application")
    Set Mess = OutlookApp.CreateItem(olMailItem)
    With Mess
        .Subject = Subj
        .HTMLBody = Emess
        .Attachments.Add (Attach1)
        .Recipients.Add Recip
        .Send
    End With
    
    Else
    
    If attach3 = "" Then
    
    Set OutlookApp = CreateObject("Outlook.Application")
    Set Mess = OutlookApp.CreateItem(olMailItem)
    With Mess
        .Subject = Subj
        .HTMLBody = Emess
        .Attachments.Add (Attach1)
        .Attachments.Add (attach2)
        .Recipients.Add Recip
        .Send
    End With
        
    Else
    
    If attach4 = "" Then
    
    Set OutlookApp = CreateObject("Outlook.Application")
    Set Mess = OutlookApp.CreateItem(olMailItem)
    With Mess
        .Subject = Subj
        .HTMLBody = Emess
        .Attachments.Add (Attach1)
        .Attachments.Add (attach2)
        .Attachments.Add (attach3)
        .Recipients.Add Recip
        .Send
    End With
    
    Else
    
    If attach5 = "" Then
    
    Set OutlookApp = CreateObject("Outlook.Application")
    Set Mess = OutlookApp.CreateItem(olMailItem)
    With Mess
        .Subject = Subj
        .HTMLBody = Emess
        .Attachments.Add (Attach1)
        .Attachments.Add (attach2)
        .Attachments.Add (attach3)
        .Attachments.Add (attach4)
        .Recipients.Add Recip
        .Send
    End With
    
    Else
    
    If attach6 = "" Then
    
    Set OutlookApp = CreateObject("Outlook.Application")
    Set Mess = OutlookApp.CreateItem(olMailItem)
    With Mess
        .Subject = Subj
        .HTMLBody = Emess
        .Attachments.Add (Attach1)
        .Attachments.Add (attach2)
        .Attachments.Add (attach3)
        .Attachments.Add (attach4)
        .Attachments.Add (attach5)
        .Recipients.Add Recip
        .Send
    End With
    
    Else
    
    If attach7 = "" Then
    
    Set OutlookApp = CreateObject("Outlook.Application")
    Set Mess = OutlookApp.CreateItem(olMailItem)
    With Mess
        .Subject = Subj
        .HTMLBody = Emess
        .Attachments.Add (Attach1)
        .Attachments.Add (attach2)
        .Attachments.Add (attach3)
        .Attachments.Add (attach4)
        .Attachments.Add (attach5)
        .Attachments.Add (attach6)
        .Recipients.Add Recip
        .Send
    End With
    
    Else
    
    Set OutlookApp = CreateObject("Outlook.Application")
    Set Mess = OutlookApp.CreateItem(olMailItem)
    With Mess
        .Subject = Subj
        .HTMLBody = Emess
        .Attachments.Add (Attach1)
        .Attachments.Add (attach2)
        .Attachments.Add (attach3)
        .Attachments.Add (attach4)
        .Attachments.Add (attach5)
        .Attachments.Add (attach6)
        .Attachments.Add (attach7)
        .Recipients.Add Recip
        .Send
    End With
    
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    
    
' Change text back to Scorecard Analysis
    If Range("D26") = Range("Q31") Then
        Range("D26") = "SCORECARD ANALYSIS"
    Else
    
' Change text back to Scorecard Analysis
    If Range("D27") = Range("Q31") Then
        Range("D27") = "SCORECARD ANALYSIS"
    Else
    
' Change text back to Scorecard Analysis
    If Range("D28") = Range("Q31") Then
        Range("D28") = "SCORECARD ANALYSIS"
    Else
    
' Change text back to Scorecard Analysis
    If Range("D29") = Range("Q31") Then
        Range("D29") = "SCORECARD ANALYSIS"
    Else
          
' Change text back to Scorecard Analysis
    If Range("D30") = Range("Q31") Then
        Range("D30") = "SCORECARD ANALYSIS"
    Else
    
' Change text back to Scorecard Analysis
    If Range("D31") = Range("Q31") Then
        Range("D31") = "SCORECARD ANALYSIS"
    Else
    
' Change text back to Scorecard Analysis
    If Range("D32") = Range("Q31") Then
        Range("D32") = "SCORECARD ANALYSIS"
    Else
    
' Change text back to Scorecard Analysis
    If Range("D33") = Range("Q31") Then
        Range("D33") = "SCORECARD ANALYSIS"
    Else

' Change text back to Scorecard Analysis
    If Range("D34") = Range("Q31") Then
        Range("D34") = "SCORECARD ANALYSIS"
    Else
    

    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    
' Get the next cell in the range
    Next Mycell
        
    End If
    End If
    
    
End Sub


Reply ↓  Report •

#14
October 11, 2018 at 09:47:05
re: "Not having much luck with it but will keep trying to utilise the coding"

If you'd tell me what ""Not having much luck" means, I might be able to help. Otherwise, I'm flying blind.

As far as "untested", the only part that was "untested" was the actual creation of the emails and adding the attachments. The main reason for that is that I didn't feel like putting a bunch of path/file names in cells so that the code could use them as attachments.

I do know that when I have the code create a MsgBox with the contents of the drop down choices, it works fine.

Again, you would need to tell me when/where it fails before I could offer any suggestions as to how to fix it. Nothing personal, but it may not be the code, it may be the way that you are implementing it.

If I get a chance, I'll try to create a worksheet that will allow me to actually attach files to an email as a means to test the actual code that I posted.

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


Reply ↓  Report •

#15
October 19, 2018 at 22:05:37
Hi DD03,
Sorry for not coming back sooner

  'Add attachments from D26:D32 Based On numAttach value
          If numAttach > 0 Then
            For attachCell = 26 To (26 + numAttach - 1)
             .Attachments.Add (Sheets("EBODY").Range("D" & attachCell))
            Next
          End If
        .Recipients.Add Recip
        .Send
    End With

This line of the code is causing the issue

.Attachments.Add (Sheets("EBODY").Range("D" & attachCell))

If I am correct with the code, if numattach =3, then attachCell should equal 28 on the first loop through
However attachCell does not change from 26 and comes up with

Run Time Error 438

immediately

This is why Ive taken my time to come back, trying to solve these issues myself so as to learn more about coding and how it comes together
Just cant see where the problem is with this


Reply ↓  Report •

#16
October 20, 2018 at 07:21:01
For the time being, I have reset the Best Answer so that we are not continuing to work on issues within a Solved thread. If we never get the shortened version of the code working, you can choose whichever answer you think is best.

For now, let me address your current question:

re: "If I am correct with the code, if numattach =3, then attachCell should equal 28 on the first loop through."

No, that is not correct. attachCell always starts at 26, therefore "on the first loop through" it will always be 26. attachCell will then increment each time through the loop based on the value of numAttach. For example, with a For-Next loop initiated with this instruction...

For attachCell = 26 To (26 + numAttach - 1)

...and numAttach equal to 3, the starting value for the loop is 26 (always) and the ending value is:

26 + 3 - 1 = 28

In other words, VBA will do the math and use this to process the For-Next loop:

For attachCell = 26 To 28

Therefore the Loop should refer to the value in D26 on the 1st pass, D27 on the 2nd and D28 on the 3rd. Since numAttach = 3, we get three loops and therefore (hopefully) 3 attachments using the values in D26:D28.

EDIT: See my next post below.

I modified your code snippet to produce MsgBoxes instead of attachments and it works fine. It creates 3 MsgBoxes showing the contents of D26:D28. Again, since I have not tested this by actually trying to add attachments to an email, for all we know, it won't work. I try to get to that.

Try this and tell me what happens.

Sub testSnippet()
numAttach = 3
'Add attachments from D26:D32 Based On numAttach value
          If numAttach > 0 Then
            For attachCell = 26 To (26 + numAttach - 1)
             '.Attachments.Add (Sheets("EBODY").Range("D" & attachCell))
             MsgBox Sheets("EBODY").Range("D" & attachCell)
            Next
          End If
        '.Recipients.Add Recip
        '.Send
    'End With
End Sub


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

message edited by DerbyDad03


Reply ↓  Report •

#17
October 20, 2018 at 08:45:36
✔ Best Answer
Try the following. It seems like VBA doesn't like it when a cell reference is used in the Attachments.Add instruction. I think it's because it is not seeing the contents as a text string. Using a variable to hold the file path, like you did in your code, seems to work.

I tried this with 3 files from 3 different file locations and it attached all three.

 'Add attachments from D26:D32 Based On numAttach value
          If numAttach > 0 Then
            For attachCell = 26 To 26 + numAttach - 1
               nxtAttach = Sheets("EBODY").Range("D" & attachCell)
               .Attachments.Add nxtAttach
            Next
          End If

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

message edited by DerbyDad03


Reply ↓  Report •

#18
October 21, 2018 at 10:45:03
Hi DD03
I finally got your code to work. However …..
It will not work if the 'attachment' comes from a Data Validated Cell containing a list
I had to make 'B26' an if statement to the effect '=IF(D26=0,"",D26)' and so on until 'B33', and then adjust the code slight to resemble this
Not sure why this is the case, and you may be able to confirm back to me why?, but nonetheless, your coding is exemplary and I really appreciated your help

Reply ↓  Report •

#19
October 21, 2018 at 16:12:19
re: "I finally got your code to work."

Which code? The code in response #17?

That code works just fine for me. I have Data Validation lists in D26:D32 and the code attaches the choices and sends the emails.

I don't know why it doesn't work for you.

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

message edited by DerbyDad03


Reply ↓  Report •

#20
October 22, 2018 at 13:53:04
Hi DD03
The code in response #17
I have just retried running the code in my little spreadsheet, changed it back from "B" to "D"
and my test emails were sent correctly
Not sure why it was coming up with an error, but as you stated, it know works fine

Many thanks for your help
I only really work on my spreadsheet when I find something that I want to make work or produce, so I'm still relatively basic with my code knowledge
Again many thanks


Reply ↓  Report •

#21
October 22, 2018 at 14:12:52
I'm glad you got it working.

I also found another solution that I should have thought of sooner. You can eliminate the need to use the nxtAttach variable by using this .Add instruction:

.Attachments.Add Sheets("EBODY").Range("D" & attachCell).Value

By adding .Value after the cell reference, VBA ignores everything else about the cell except the contents. When you reference a cell via VBA, it gathers a lot of information about the cell object just in case it needs it later. When you add .Value (or any other valid property associated with the cell) that is all that VBA will use. Apparently trying to use the "generic" cell reference - which works in a lot of other situations - doesn't work with the .Attachments.Add instruction.

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


Reply ↓  Report •

Ask Question