Posts by exelerated

Re: QC/Debug/Compatibility Worksheet E-mailer - $50

Cool, thanks!

Hi!

I have paid Ozgrid the 10% -- I need someone to take a look at a mostly-completed e-mail tracker and debug a few issues -- these errors are present with Excel 2010, so you MUST be able to work with Excel 2010 or greater in order to take this on. If you cannot replicate the bugs, you cannot work on this.

I have an e-mail tracker in attachment -- it mostly functions but I need to correct the following errors:

ENCOURAGER tab --
When you click "Send e-mail(s)", it randomly selects one of the 5 "NEXT UP" tabs, and populates an e-mail with the agent name, MTD, YTD, etc. -- the issues are:

1) Next Up 2 displays an error when sending and sends the code into debug mode

With chtChart
[COLOR=#ff0000][B]                .Paste[/B][/COLOR]
                .Parent.Width = rngToCopyAsPicture.Width
                .Parent.Height = rngToCopyAsPicture.Height
                .Export strTempName, "BMP"
                .Parent.Delete
            End With

2) Next Up 3 returns a blank e-mail with only a square shape -- no image of the contents is actually taken, as it is with Next Up 1, 4 and 5.

INVESTIGATOR Tab --

1) This tab is designed to keep track of which agents receive which e-mails, and how many times. That aspect seems to work.
2) When you click "Send E-mail", it does not copy the selected message into an Outlook email as it should -- it returns an error that sends the code into debug mode. It appears to get caught by the same line of code as with Next Up 2:

With chtChart
[COLOR=#FF0000][B]                .Paste[/B][/COLOR]
                .Parent.Width = rngToCopyAsPicture.Width
                .Parent.Height = rngToCopyAsPicture.Height
                .Export strTempName, "BMP"
                .Parent.Delete
            End With

--

I would like to resolve these issues and have this working flawlessly. I am not aware of any other issues at present. I would like to have this completed within a few days to a week.

Thanks everyone!

Re: E-mail Generator/Tracker - Store variables & populate templates - $75.00USD

Good to hear, thanks!

I see your point -- for the sake of simplicity, it is enough that the e-mail is generated for it to count towards the sent tally. If the user closes out of the e-mail without sending, it's OK for it to still be counted in the sent columns.

Re: E-mail Generator/Tracker - Store variables & populate templates - $75.00USD

Hi SMC!

How's it coming?

Re: E-mail Generator/Tracker - Store variables & populate templates - $75.00USD

Sorry about that -- you can unmerge them as necessary

Re: E-mail Generator/Tracker - Store variables & populate templates - $75.00USD

Hi SMC!

1) Yes, the Landmark tab should send an e-mail to All Users Distribution. For the template on the All Users E-Mail tab, it will use the vaiables inputted by the user on the Landmarker tab (hence whatever Agent name is selected in the drop down box will populate the (NAME) bar on the All Users E-Mail template, but the e-mail itself will send to the All Users Distribution email)

2) Yes, those lists are intended to populate the drop down menus above.

Thanks for your questions!

Re: E-mail Generator/Tracker - Store variables & populate templates - $75.00USD

Oh, sorry I didn't explain that well. The MTD will be a number fed in by the user (or a link to another excel sheet), but the MILESTONE would be updated via a formula that would change based on the MTD. So when the MTD is changed, the MILESTONE will automatically find the next nearest $500. So if the inputted MTD is say $2131.00, then the MILESTONE will have a formula that automatically updates to be $2500.00. The e-mail status will be NOT SENT. When the user sends the e-mail, it will change to SENT. Later, when the MTD changes to something like $2548.00, the MILESTONE will change accordingly, to $3000.00, and the e-mail status will change to NOT SENT (because the last e-mail sent was regarding the $2500 milestone, and this e-mail would be about the $3000.00)

Re: E-mail Generator/Tracker - Store variables & populate templates - $75.00USD

Cool, so do the comments make sense? Does this seem like something you can take on?

Re: E-mail Generator/Tracker - Store variables & populate templates - $75.00USD

Oh great, glad to hear it!

Re: E-mail Generator/Tracker - Store variables & populate templates - $75.00USD

Hi SMC!

Are you still interested in working on this, or is anyone else available?

Re: E-mail Generator/Tracker - Store variables & populate templates - $75.00USD

hey SMC! let me know if those files work better for you. If not, I can always send you screenshots of the comments.

Re: E-mail Generator/Tracker - Store variables & populate templates - $75.00USD

forum.ozgrid.com/index.php?attachment/72198/
forum.ozgrid.com/index.php?attachment/72199/

I'm sorry, I just saw this. I've attached the xlsb format you mentions. I hope this works better!

I am offering $75 for this work -- I have paid the $7.50 to Ozgrid via PayPal already.

I have an excel document where the user will be able to input a list of names, e-mails and a few other numbers and details and I then want that information to be stored in variables and called upon in various ways to generate e-mails. My excel doc already has various pre-made templates for the e-mails, and you will note areas where parantheses are used to indicate that a variable should be inserted there. Due to their image-based, visual nature, I want the e-mail generated to be a .png file embedded into the body of the e-mail via Outlook.

This is a broad overview, but I have attached my base excel sheet and have put extensive notes on it to help explain in specific what I need it to do. Please take a look before accepting the job.

All best,

Re: Generate dynamic range of cells based on last row used

You're brilliant!! Thank you

Re: Generate dynamic range of cells based on last row used

Thanks for your help, that's exactly what I needed!!

Now I'm having another slight wrinkle. I'm able to generate the image and put it to Outlook just fine, and it captures most of the selection, but for some reason it always cuts off the actual last row. It's consistently 2 rows short. Any suggestions? Thanks again for your help everyone!!

Sub CopyRangeAndMailAsPicture()


Dim sh As Worksheet
Dim LastRow As Long
LastRow = Cells(Application.Rows.Count, "K").End(xlUp).Row




    
Set sh = Sheets("Output")
Application.Calculation = xlManual
With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With




Dim TempFilePath As String
Set OutApp = CreateObject("outlook.application")
Set outMail = OutApp.CreateItem(olMailItem)
With outMail
    .Display
    .Subject = [Subject]
    .To = "[email protected]"
    .CC = "[email protected]"
     .BCC = ""




     
Call createJpg("Output", ("G8:R" & LastRow), "FinalImageFile")
    TempFilePath = Environ$("temp") & "\"
    .Attachments.Add TempFilePath & "FinalImageFile.png", olByValue, 1




    .HTMLBody = "<img src='FinalImageFile.png'" & ">" _
        & "<br>" & .HTMLBody
    .Display
    '.Send
End With




Set sh = Nothing




End Sub

Hello!

I have a range of cells that begin at G8 and end at R -- the variable factor is that it could be anywhere from R20 to R100 depending on dynamically changing content. I have this great bit of vba code that assists with e-mailing a screenshot of said range with the click of a button. The e-mail snapshot part works great, the problem I'm having it how to move from a pre-set, fixed range (like G8:R30), and have it dynamically update based on the amount of rows used (G8:R38, G8:R100, etc.)

Thank you for any insight you can offer!

Sub CopyRangeAndMailAsPicture()


Dim sh As Worksheet








    
Set sh = Sheets("Output")
Application.Calculation = xlManual
With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With




Dim TempFilePath As String
Set OutApp = CreateObject("outlook.application")
Set outMail = OutApp.CreateItem(olMailItem)
With outMail
    .Display
    .Subject = [Subject]
    .To = "[email protected]"
    .CC = ""
     .BCC = ""

Call createJpg("Output", "G8:R30", "FinalImageFile")

TempFilePath = Environ$("temp") & "\"
    .Attachments.Add TempFilePath & "FinalImageFile.png", olByValue, 1




    .HTMLBody = "<img src='FinalImageFile.png'" & ">" _
        & "<br>" & .HTMLBody
    .Display
    '.Send
End With




Set sh = Nothing




End Sub








Sub createJpg(Namesheet As String, nameRange As String, nameFile As String)
ThisWorkbook.Activate




Worksheets(Namesheet).Activate




Set Plage = ThisWorkbook.Worksheets(Namesheet).Range(nameRange)
Plage.CopyPicture
With ThisWorkbook.Worksheets(Namesheet).ChartObjects.Add(Plage.Left, Plage.Top, Plage.Width, Plage.Height)
    .Activate
    .Chart.ChartArea.Format.Line.visible = False
    .Chart.Paste
    .Chart.Export Environ$("temp") & "\" & nameFile & ".png", "PNG"
End With
Worksheets(Namesheet).ChartObjects(Worksheets(Namesheet).ChartObjects.Count).Delete




Set Plage = Nothing




End Sub

Re: Add outline border to dynamic range of cells

Thank you! This works great

Hey guys!

I have a range of data that starts at B5 through K5, and the number of rows down will grow or shrink dynamically depending on how much data has been inputted, so there is no set endpoint. Is there a way to generate an outline border around the entire range of cells used?

Also, if I want to refer to that variable last row (i.e. if the range is B5:K33 one day, then B5:K42 the next) in vba for other coding purposes, is there a way to mention it in code?

Thanks for your help!

Re: Userform to record food orders (generate data to defined template) - $40 USD

Thanks for the quick work, Kris! From what I can see visually, this looks perfect. However, when I open it all macros are disabled automatically because excel finds an error with it --

"We found a problem with some conent in 'Moonlight Pass On'"
"Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.
Removed Part: /xl/vbaProject.bin part. (Visual Basic for Applications (VBA))"

Let me know what you think (never gotten this error before). I'll send payment as soon as I receive it.

Thanks again!

Re: Userform to record food orders (generate data to defined template) - $40 USD

Hi Kris!

This is a really great format so far, but it still doesn't quite do what I need it to. I can tell the overall structure is 100% there, it just needs some tweaks. I have tried fiddling with it, but I'm just making it worse. I am open to investing another $40 into this if you are able to make some more modifications:

1) Currently, only the 1st notes box will transfer to the output -- notes 2, 3 etc. do not show up. Can this be fixed?

2) Can the "refresh dates" be somehow built into the SHOW ORDER button so that we don't have to click multiple buttons

3) Completely eliminate the lookup by room AND date feature -- it should be lookup by date only.

4) Most importantly -- on the output tab, I just want the gray header, early wake-ups and breakfast pre-orders headers to be posted once and fixed permanently on the output page (as opposed to dynamically generated with each order). The only thing that the SHOW ORDER button should generate is each order itself (essentially everything from the room number and time down through the order, with no extra headers, etc)., and it should place is all within the one overall template that is there.

Let me know if you're able to work on this for an additional $40. If not, I will see if someone else can take this up. Thanks again!