Type Mismatch Compile Error: Can't figure Out

[Pauly723]

Hello,

I've been able to figure out that there can only be one set of parentheses in the .HTMLBody portion of the code. However, I can't seem to figure out how to call forth the values from each range and have them populate within the email. To illustrate the type mismatch error that I'm receiving I purposely put everything in parentheses in the .HTMLBody section. Then, under the .subject section, I tried to call forth the applicable value using, & Answer13(Counter, 1) = ProjectNumber, which gave me the type mismatch error. My goal is to be able to have the email populate with the values from the ranges in the specific locations of the subject and body of the email. I'm not sure if this is possible. Someone, please assist. Thank you

Please Login or Register  to view this content.

[Norie]

ProjectNumber is an array, you can't compare an entire range to a single string value.

Mind you I'm not sure you are trying a comparison.

What are you trying to do with ProjectNumber here?

Please Login or Register  to view this content.

Do you actually want to refer to a specific item in the array and concatenate it to "Strong Closeout Possibility:" & Answer13(Counter, 1) r?

If you were you would use ProjectNumber(Counter,1) and you would change the = to a &.

Please Login or Register  to view this content.

PS Have you considered not using arrays for this?

Please Login or Register  to view this content.

[Pauly723]

Wow, you're a genius That pulled the info for me.
If there's a simpler solution to this than arrays then please, I'm all ears. I'm just starting to learn VBA and arrays, although lengthy, have been very effective for me thus far. Thanks for your help

[Pauly723]

Thanks for your reply This is really cool and gives me another approach.

I tried running your code and came up with similar results for the .HTMLBody section wherein the values are not being pulled from the answer ranges and populated within the email. Instead, the email that's being created is just showing the exact code that's been written. Do I need to add additional quotes, perhaps & signs, or parentheses somewhere within the .HTMLBody section to enable the values to populate within the email? Thanks again for your help

[Norie]

Never noticed the HTMBody part.

You might want to do something like this.

Please Login or Register  to view this content.

PS Still not sure about the '='s, are they meant to be part of the email body or the code?

[Pauly723]

Nice, thank you I tried entering this into the code you had before and am getting a compile error: next without for. I'm not sure why as there is a - For Each cl In rng followed by a Next cl. I also had to define both strHTML and Counter and I'm not sure if they're identified properly. Here's the code I'm using. I know it's so close, thanks again for your help

Please Login or Register  to view this content.

[Norie]

There was an End If missing and a couple of other problems.

Please Login or Register  to view this content.

[Pauly723]

Hi again, sorry to bother you, but would you mind assisting me with one additional piece of code? It will be inserted into the code you've so generously helped me with already. What I'm trying to do is essentially copy and paste the row of data corresponding to the email type answer into a separate sheet with the same email type name for tracking purposes. The line of code I'm struggling with is the "Worksheets" line and it's giving me a run-time error 9 subscript out of range with this entire line highlighted. Thanks again for your help and please let me know if you need any further information

Please Login or Register  to view this content.